mysql version 5.5.55-0 Ubuntu-14.04.1

innodb used

yesterday: I downloaded a database dump, a query without an index was executed for 10 seconds, I created an index — the query with the index was executed for 4 seconds

today: downloaded the same database dump as yesterday, the query without an index is executed for 10 seconds, created the same index as yesterday - the query with the index is executed for 12 seconds

what could be the problem?

EXPLAIN indicates that the index is in use.

I tried to turn off all the programs that eat a lot of memory (6000 MB became free) as a result is not the problem

  • Using an index can both speed up and slow down a query. it is necessary to carefully consider the request itself, the execution plan and at the same time analyze the data on which it works. For example, searching 10 entries by index out of 1000 is much faster than, same search without index, but retrieving 500 entries out of 1000 by index is slower than iterating through a table without using an index. - Mike
  • @Mike vseravno not clear why with the same actions as yesterday, the query is executed with the index slower - Igor Laktionov
  • @Mike and removing the index and creating anew, only increases the sample time - Igor Laktionov
  • those. did you upload a dump from the same file and before you filled it in, you explicitly deleted the table? And the index and yesterday and today created after filling all the data? in general, innodb all data lies in one large file and when re-flooded, data may lie in other blocks and the index may physically be on the disk further and the heads will have to run further, which of course will affect the sampling speed. true 4 and 12 seconds is somehow too big a difference ... - Mike
  • @Mike Yes, I deleted the database, uploaded the new dump, created the index, thanks for the hint, now I’ll try to use the scientific method to delete the database and cast new =) - Igor Laktionov

0