MySQL Forums
Forum List  »  Performance

Re: Best Index Strategy
Posted by: Devrishi Shandilya
Date: August 16, 2015 11:53PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2033
August 09, 2015 11:35PM
808
August 10, 2015 07:02AM
759
August 10, 2015 07:17AM
739
August 10, 2015 11:30PM
735
August 12, 2015 10:53PM
709
August 15, 2015 04:34PM
Re: Best Index Strategy
713
August 16, 2015 11:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.