Before finalizing my queries, I use MySQL Workbench to explain it to make sure the performance is good. I always notice that the performance of the queries with joins on multiple tables varies at times. Then I start indexing my tables to make sure that the Explain is happy and scanning minimum records before handing over the data.
But with one of my query, even after adding an appropriate index I was not getting the performance I was expecting. I asked Workbench to explain the query and what? MySQL was not using my newly added index which could optimize the query.
I tried FORCE INDEX and MySQL liked it. It started using the index which I forced it to. But I was not happy at forcing MySQL to use my index. I wanted it to do it itself.
After researching a bit, I came across STRAIGHT_JOIN and without wasting any time, I added it to my query and what? MySQL started using my index without being forced to.
Now the question was, how the STRAIGHT_JOIN works and is different from INNER JOIN.
With my research I found that STRAIGHT_JOIN is an inner join which returns the matching records like the later. But what it does different is; it doesn't the change order in which the tables are joined. Unlike an INNER JOIN, it reads the left table first and then the right table. Generally, the table returning smaller result should be put on the left but we are habitual to put the table returning the larger result on the left.
But just putting the correct table on the left doesn't always help. MySQL optimizer sometimes chooses the poor query plan to not to use the correct index or not to use the index at all but not all the time.
Does that mean we should always start using the STRAIGHT_JOIN over INNER JOIN? NO
Then, how to do it? Observe the query with Explain and check if it is optimal. If not, try re-structuring it to match the expectations. If all the attempts fail, then you know how to fix it at the end.
But STRAIGHT_JOIN shouldn't be used always because with the time your table may grow, your indexes may change and with that your optimal query may become sub-optimal. Your indexes, query hints at then may become outdated now and the performance of your query may slow down over the time.
So, be more flexible to let MySQL optimizer take the decision to use the optimal hints instead of forcing it to.
Very Useful
ReplyDelete