I have a column "name" in a table and there are at least 10 million records. I've added an index on this name column. Now, for the first initial few searches, it was taking more than 10 seconds to return a single answer (whether the name was present or not) but suddenly, it gives me the result in less than 0.1 seconds. Am I missing something? I know MySQL maintains a cache of the recent queries but the queries I am trying are pretty random... Any ideas as to why this is happening?
views:
53answers:
2
A:
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.
via MySQL.com
hsz
2009-12-09 01:04:31
Thanks but my question was more geared towards understanding what was happening after adding an index. I know how an index works but I was confused by the change in behavior.
Legend
2009-12-09 01:06:16
+4
A:
Your query is simply warming the index caches. This has nothing to do with the query cache. InnoDB indexes particularly take some hits to warm up as their the index cardinality values are not stored in the table files. The initial queries will popluate the InnoDB buffer pool with the index data they encounter as defined by the value specified by innodb_buffer_pool_size in my.cnf. Though less important for MyISAM tables, the value key_buffer_size can be adjusted similarly.
MySQL's InnoDB performance tunning chapter provides a good entry point into figure out what is going on: http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
zznate
2009-12-09 01:31:30