| CREATE TABLE IF NOT EXISTS `$tableName` ( |
| `id` int(10) unsigned NOT NULL auto_increment, |
| `name` varchar(64) NOT NULL default '', |
| `email` varchar(64) NOT NULL default '', |
| `password` varchar(64) NOT NULL default '', |
| `dob` date default NULL, |
| `address` varchar(128) NOT NULL default '', |
| `city` varchar(64) NOT NULL default '', |
| `state_id` tinyint(3) unsigned NOT NULL default '0', |
| `zip` varchar(8) NOT NULL default '', |
| `country_id` smallint(5) unsigned NOT NULL default '0', |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `email` (`email`), |
| KEY `country_id` (`country_id`,`state_id`,`city`) |
|
)
SELECT Queries:
READ_PK_POINT
SELECT name FROM $tableName WHERE id = %d
This query performs an access to table data by Primary Key
READ_KEY_POINT SELECT name FROM $tableName WHERE country_id = %d limit 5
Access to table data by single value of key
READ_KEY_POINT_NO_DATA SELECT state_id FROM $tableName WHERE country_id = %d limit 5
Access to table data by single value of key and read the returned data from index READ_PK_POINT_INDEX SELECT id FROM $tableName WHERE id = %d
Access to table data by single value of PK and return value from PK READ_PK_RANGE SELECT min(dob) FROM $tableName WHERE id between %d and %d
Access to table data by range values of PK READ_PK_RANGE_INDEX SELECT count(id) FROM $tableName WHERE id between %d and %d
Access to table data by range values of PK and read data only from PK READ_KEY_RANGE SELECT name FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50 Access to table data by range values of key
READ_KEY_RANGE_NO_DATA
SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50
Access to table data by range values of key and read data only from index
READ_FTS SELECT min(dob) FROM $tableName Perform Full table scan |