… do Join not on primary key and have query to read data for both tables. If the query would be index covering… lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on… bound workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if you…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
….vid index for it. And no surprise it was way too slow, the number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…, let’s check if that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql> EXPLAIN SELECT * FROM cast_info… that whenever the covering index technique is available, this is always preferred over the ICP optimization: mysql> EXPLAIN SELECT role_id FROM… with its last GA release? Then I invite you to join me at the webinar I have prepared for this Friday…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
…from `test`.`sbtest` `t1` join `test`.`sbtest` `t2` where…| PRIMARY | 4 | func | 1 | Using index; Using where | +—-+——————–+——–+—————–+—————+———+———+——+———+————————–+ … EXPLAIN EXTENDED is very valuable addition to EXPLAIN…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… it connect over the SSH tunnel to query EXPLAIN and find out the index usage on the remote server, and store the…’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables | +———————–+ Now let’s run some queries… FROM index_usage AS iu INNER JOIN ( SELECT query_id, db, tbl, SUM(cnt) AS total_cnt, COUNT(*) AS variations FROM index_usage…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… which just joined 2 tables… I ran EXPLAIN for it and got 200 of rows in the result set just for explain due… Tables views which you join together you may have serious issues because such tables do not get any indexes. Let me now… a very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… field from the table which is not first in the join order index can’t be used. Sometimes it means breaking normalization… 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: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…join in both directions. Explain for query Q1.1: mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from lineorder join… +——————-+——————+——-+——+——+———+—————–+ | `ssb`.`lineorder` | GEN_CLUST_INDEX | 6001 | 2095 | 3906 | 964974…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… the dimension columns are indexed on every table, and there’s a separate index on every column …to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table | type | rows | Extra | …
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
… space id 0 page no 52 n bits 72 index `GEN_CLUST_INDEX` of table `test/t` trx id 0 14615 lock…-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 +- Bookmark…
