Resolved: M query fetch time increased after creating an index for the salaries table!
M query fetch time increased after creating an index for the salaries table!
I have the same problem. The query took almost double the duration.
Same as you, it took more than the double of the duration.
Me too, and there were 0 records returned after running the query.
Hi Ialit, AbdElrahman and Sawsan!
Тhanks for reaching out!
The final effect from using an index depends on local technology and hardware as well the type of data being used. There are many factors that will determine the total time of a query execution, and whatever index is chosen, it is always tested prior to being applied across the entire company.
Depending on the circumstances, sometimes it might happen that when a certain table has more than one index it would affect the performance negatively. We have to be careful to set the proper amount of columns to be indexed as in the end it might worsen the performance. So, while indexes are meant to improve the performance of a query, they may worsen it. Therefore, always remember to test an index prior to applying it.
Also, please remember that the purpose of this lecture is to show you how to use MySQL indexes - even if in this case you did not improve the performance, using indexes follows the structure provided in the lecture.
Hope this helps.
Best,
Martin
Just chiming in to say my result was the same. Increased duration/fetch times with the index. I think this is actually an excellent example to show why it isn't necessary (or beneficial) to index every column. I like this example because I was thinking exactly that. Why not just index every column for the best performance? This example proves it is not always better to have an index on every frequently used column.