Re: Best Index Strategy

[Devrishi Shandilya]

Hi Rick,

Thanks for update. I did flush status and run queries with and without force index hint and here are the handler status, Pl suggest on the same


mysql> SHOW SESSION STATUS LIKE 'Handler%'; (Original query without force index and taking 3.50 sec. to execute even after FLUSH STATUS)
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 342375 |
| Handler_read_last | 1 |
| Handler_read_next | 513558 |
| Handler_read_prev | 171186 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+--------+
18 rows in set (0.00 sec)


mysql> SHOW SESSION STATUS LIKE 'Handler%'; (modified query with force index and taking 0.08 sec. to execute even after FLUSH STATUS)
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 43 |
| Handler_read_last | 0 |
| Handler_read_next | 42 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 20 |
| Handler_read_rnd_next | 22 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 21 |
+----------------------------+-------+
18 rows in set (0.00 sec)

I did one more thing, I dropped all indexes from table soas and then first I create soas.(address_type,telephone) then Primary and other index. Now my origional query is using soas.(address_type,telephone) index without force index hint.