| 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
Access to table data by single value of key
READ_KEY_POINT_NO_DATA
SELECT state_id FROM $tableName WHERE country_id = %d
Access to table data by single value of key and read the returned
data from index
READ_KEY_POINT_LIMIT
SELECT name FROM $tableName WHERE country_id = %d limit 5
Access to table data by single value of key and LIMIT result
READ_KEY_POINT_NO_DATA_LIMIT
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 and LIMIT result
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
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
Access to table data by range values of key and read data only from
index
READ_KEY_RANGE_LIMIT 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 with LIMIT
READ_KEY_RANGE_NO_DATA_LIMIT
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 with LIMIT
READ_FTS
SELECT min(dob) FROM $tableName
Perform Full table scan |