From 2.3s to 22ms in 1 minute. Mysql index optimisation

In my little free time i developed for Tfm Group Software a small php application that read from a mysql table , made some calculation and presented a small table as result. In a cron job at every 5 minutes some data were inserted in the database. So far so good. The application was ok  and did it’s job . Another projects came and i forgot about it. However the data kept being inserted in the table. Several months later  i tried to load the result page . And for my surprise it loaded in 2.3 seconds. For a moment i was thinking that i did something wrong or the application was modified. Well it didn’t . In the database i had 375k rows but no indexes. The most used column was `insert_date` . So instantly i checked if i used index on it . Well i didn’t . So the quick fix came naturally:

create index a1 on tfm (insert_date);

And the page load time decreased magically from 2.3s to 22ms . This is a reminder to power of myql indexes in tfm linux or any other linux of your choice.

 

Leave a Reply