… a good tradeoff. Q: Is the tuple comparison independent of sorting order? Wouldn’t it be a problem if the keywords… each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first, by looking up… in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot, the straight join, can you explain more…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In MySQL 5.0 with addition of greedy join the problem on looking at too many table join combinations in joins with very large tables is…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’join…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ mrr_buffer_size=32M optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join… selective WHERE clause on an indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL… WHERE clause on a non-indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… the join in this case because there is really no join: mysql> EXPLAIN select SQL_NO_CACHE c.city from cities_join c JOIN states… perform the join for each row to retrieve it. This also means sort can’t be done by index and extra sort pass (filesort) is required, which also makes MySQL to store Join result…
Comment: Four ways to optimize paginated displays
November 18, 2010 By 27 Comments
Using the estimation of EXPLAIN is a really cool idea. For most of my paginations, … and a key on rank CREATE TEMPORARY TABLE _tmp (KEY SORT(rank DESC)) SELECT id, rank FROM table; # add a primary… AUTO_INCREMENT, DROP INDEX SORT, ORDER BY rank; # scan the primary key and get the result, maybe add a join to get real…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… or functions will block index usage for order by. Sort by column in leading table if you have JOIN with ORDER BY … LIMIT… use FORCE INDEX to force it to use appropriate index. One more note about ORDER BY … LIMIT is – it provides scary explain statements… log as query which does not use indexes, even if it is quite fast: mysql> explain select * from test order by k…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…where secondary key tuples are buffered and then sorted by the primary key values, and then …index lookup is performed. As I explained at the start of the post that traditional index lookup (for non-index…well. But this works only with joins and specifically with Block Access Join Algorithms. So I am …
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
… bits 72 index `GEN_CLUST_INDEX` of table `…sort of goodies can we think of here? mysql> pager mk-visual-explain PAGER set to ‘mk-visual-explain‘ mysql> explain select * from sakila.film inner join sakila.film_actor using(film_id) inner join sakila.actor using(actor_id); JOIN…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…web applications which do group by one field sort by another and then use LIMIT to …sure all where clauses are using indexes, try to have queries index covered and avoid joins as much as possible….Index or it can be slow on large data sets if external filesort is used so make sure to check explain…
