![]() ![]() Now, when we run the SQL query, we're including our variable in the INNER JOIN which is allowing the query execution to greatly limit the results of the cross-product. * inspecting the primary key of the table, it can get this ID instantly usingġ0 - We want the most recent 10 comments, skip over first 10 IDs.Ĭ.id > - !! USING THE CUT-OFF ID TO LIMIT CROSS-PRODUCT !! * the INNER JOIN condition in the subsequent query. * Let's get the 11th-from-last comment ID that we can use to drastically limit This time, instead of the query scanning the blog_comment table first, it performs a full table scan of the blog_entry table in order limit the results to where the isActive=1 condition can be satisfied. | 1 | SIMPLE | c | NULL | ref | IX_join,IX_by_member | IX_join | 4 | bennadel.b.id | 14 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2605 | 50.00 | Using where Using temporary Using filesort | However, by adding the isActive condition, I was negating the ability for MySQL to apply the "backwards index scan" optimization, which fundamentally changed the query plan. Which means, when I went to add the missing isActive=1 condition to my ON clause, I assumed that I was doing little more than updating the cross-product calculation that was already in place.Īs such, I likely assumed that I didn't have to re-run the EXPLAIN on the updated SQL statement. I don't know how accurate this mental model is in relation to the nested-loop join algorithm that MySQL uses but, it keeps it simple and helps me think about index structures and performance.Īccepting that I did run an EXPLAIN on my original query and saw that it was scanning 10-rows, given my mental model for how JOINs work, I likely glossed-over the whole "backwards index scan" optimization and just assumed that the query was using standard JOIN mechanics. ![]() Meaning that the result-set of a cross-table query is calculated one JOIN at a time and then, the materialized results of that JOIN are passed-down to the next JOIN. filter() methods in ColdFusion and JavaScript. I think about SQL JOINs in the same way that I think about using. ![]() To be clear, this is a query optimization that is happening behind the scenes - it is not what the query would be doing if it were naively executing the JOIN. Basically, it's merging the requirements of ORDER BY into the ON condition of the first JOIN. In the Extra column, it states:Įssentially what (I believe) is happening here is that the query execution is just scanning the primary key index on the blog_comment table backwards, grabbing the "first" 10-rows, and then performing the JOIN conditions. Notice that the blog_comment (alias c) only has to scan 10 rows. | 1 | SIMPLE | m | NULL | eq_ref | PRIMARY,IX_info | PRIMARY | 4 | bennadel.c.memberID | 1 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bennadel.c.blogEntryID | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | index | IX_join,IX_by_member | PRIMARY | 4 | NULL | 10 | 100.00 | Backward index scan | | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ![]() Here's a truncated version of the SQL query without the isActive check ( SELECT statement is truncated because it's not relevant to the conversation): Let's take a quick look at that incomplete query first so that we can see exactly where I messed up. But, when I first authored this query, I suspect that I had forgotten about the isActive check and left it out. This boolean allows me to take blog posts offline without actually deleting them. While there is nothing of particular surprise in the blog_comment and member tables, the blog_entry table has an isActive boolean. This data is gathered across three tables: If you go to the homepage of this blog, you'll see a "Recently Posted Comments" section that shows the 10 most recent comments along with the "member" that posted the comment and the "entry" on which the comment was posted. I love how clever the MySQL query optimizer is sometimes so, I thought this might be worth a closer look. But, as I began to tweak the SQL query, I realized what happened: an earlier version of the query was using indexes but then, I made a small change that completely altered MySQL's query execution. At first glance, I thought I must have forgotten to check the performance characters of the SQL query with EXPLAIN. Yesterday, while editing some ColdFusion code for this blog, I had a face-palm moment: I came across a query - the "recent comments" query on the homepage - that was almost certainly performing a full table scan of comments. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |