… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is…` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> START… accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with…/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so it…
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 7, 2012 By 12 Comments
hi, as you said :”Then MySQL will use the key as if its only defined as … Key and returned to MySQL server which will then in turn apply the remaining parts of the WHERE clause l_shipmode in (‘AIR’, ‘AIR REG’) and l_shipinstruct = ‘DELIVER IN PERSON’.” then how do you know it ? i mean, where…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 16 Comments
… seconds. Next I ran it with a trivial WHERE clause: mysql> select count(*) from t where a = current_date; (Pop quiz: do I need…% cost for the query. If I add another WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10) = ’2008… usually can’t optimize the WHERE clause itself, only the way that the MySQL optimizer chooses to apply the clause to indexes. (However, as…
Post: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 25 Comments
… 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: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… rows: 10 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from tst where i=5 and date(d… Extra: Using where; Using filesort 1 row in set (0.00 sec) If you take a closer look to WHERE clause you will… to constant by WHERE clause. If course it works for direct constants as well. However if functions are different MySQL is not able…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… little known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use…_len is 4) and zip part where clause is applied after rows are retrived. Notice Using Where. There are even more bad news… use this approach when first key column is not in where clause at all if it has just few values. For example…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… blog post. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the… simple query with aggregation, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_id, count(*), sum…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… is so as of MySQL 5.0 The workaround is to modify statement to become SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL has triggers which can… important one is if Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide plan…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
…,B) This index can be used to lookup rows for WHERE clauses like A=5 ; A BETWEEN 5 AND 10 ; A=5… of this mistakes come from missing one very important MySQL limitation – once MySQL runs into the interval range it will not use… example if you’re just using ORDER BY without and where clauses on the table. In such case you would see “Index…
