… table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row in set (5.23 sec…% cost for the query. If I add another WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10) = ’2008…
Post: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 30 Comments
… JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there’s no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan. SELECT * FROM A, B WHERE A.ID = B.ID…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… row in set, 1 warning (0.01 sec) 4bil is out of range for unsigned column and I would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…in these cases: mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345,12346, 12347); +———————————-+ | name | +———————————-+ | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 1 row in…is not in where clause at all…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL is case…. Q: in trick #1 will “WHERE a IN (2-4)” be worse then “WHERE a IN (2,3,4)”? Another word is range for IN clause better than BETWEEN? A: IN(2…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’) AND ancestor NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL …update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s…
Post: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 21 Comments
…, which in some cases execute outside-in instead of inside-out as users expect. It’s easy to pick on subqueries in MySQL, so I’ll try to be gentle. The following query will surprise users unpleasantly: select * from a where a.id in… the query in many other cases. NOT IN(SELECT …) queries execute badly, too. (Note: putting a literal list of items in the IN() clause performs…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… are satisfied in the where clause. If a ‘const’ table contains no rows, and it is not used in an OUTER JOIN, then MySQL can… way that rows could be returned. MySQL does this by adding the WHERE clause in the query with ‘where 0′. Let’s now look at… this, MySQL can compare the constant values before completely formulating the plan. You will notice the MySQL replaces the WHERE clause with ‘where 1′ because…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… lots of single-column indexes on columns which commonly appeared in their WHERE clauses, and they’d wonder why the EXPLAIN plan for… certain types of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of… in the WHERE clause that are included in this merge operation and then perform a set intersection on the results. Here’s one situation where…
