Today the development team posted an interesting problem in staging. The table in question had 2 million records. There was an index on the column referred in the where clause. It was a range lookup. Now here is the problem ,
Query using index took 15 secs.
Query without the index took 3 secs.
Now given the background that the machine was not really configured optimally to be a database since it was a dev/staging host. After investigating, we found the following issues :
1. The query had to look at close to 570000 records to server the data.
2. MySQL optimizer chose not to use the index. But developers though it to be an anomaly and forced usage of index.
Any lookup using an index is 3x more costly for a row than a direct table access. Index really helps only when the number of records returned by the query to be minimalistic percentage of actual number of records in the table. In most other cases a full table scan will be better. Index also helps in order by and group by queries.
Why index look up is 3x more costly, in case of innodb the db needs to find the matching record in index and if it is a secondary index, it needs to looks at the pkv in the matched index record. Then it needs to jump to the primary key and locate the pkv and fetch the data stored with the pkv.
Primary key looks up in innodb are way faster due to the fact that it is clustered. The data stays together with the pkv. Hence index can take you down if not used appropriately.