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.
#Mysql optimizer turn off Offline#
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.
#Mysql optimizer turn off full#
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 kommentar(er)
