Archive for November, 2010
(I just discovered cracked.com)
#10. Searching for a NULL
SELECT * FROM a WHERE a.column = NULL
In SQL, a
NULL is never equal to anything, even another
NULL. This query won't return anything and in fact will be thrown out by the optimizer when building the plan.
When searching for
NULL values, use this instead:
SELECT * FROM a WHERE a.column IS NULL
From Stack Overflow:
I have a table of blog posts, each with a foreign key back to it's author. There are less than 15,000 entries in this table.
This query scans over 19,000 rows (per
EXPLAIN), requires a
filesort(that might be regular MySQL behavior), and takes over 400ms to return 5 rows. possibly because of the complicated
WHEREused to check if the item is actually published.SELECT blog_post.id, blog_post.title, blog_post.author_id, blog_post.has_been_fact_checked, blog_post.published_date, blog_post.ordering, auth_user.username, auth_user.email FROM blog_post INNER JOIN auth_user ON auth_user.id = blog_post.author_id WHERE blog_post.is_approved = 1 AND blog_post.has_been_fact_checked = 1 AND blog_post.published_date < = '2010-10-25 22:40:05' ORDER BY blog_post.published_date DESC, blog_post.ordering ASC, blog_post.id DESC LIMIT 5
How can I wrangle this query under control?
This query is quite simple: a filtering condition with two equalities and a range and an order by. The range in the filter fits the
ORDER BY perfectly, so the whole query could be executed using an index scan without any filesorts.
The only problem is that we have the mixed directions in
ORDER BY, and MySQL does not support
ASC / DESC clauses for the indexes.
With a simple table redesign, the problem could easily be solved: we would just need to reverse the order in the
ordering column, say, by creating its copy and storing negative values in it. This way, we could just create a composite index (with all columns ascending) and rewrite the query to use the reverse column instead. That's what many engines do, MediaWiki (which Wikipedia runs on) being one of the most well-known examples.
This solution is nice, I hear people saying,
but requires a database redesign which as we all know is never as simple as some development pundits on their blogs seem to think.
OK, this is a good point. Let's see what we can do with the current design, and, as always, create a sample table to do it:
Read the rest of this entry »