Databases
StackOverflow2013
Postsdbo
POwhy is this MySQL query so slow in MySQL 5.1.56?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POwhy is this MySQL query so slow in MySQL 5.1.56?
    primarykey
    data
    text
    <p>The following query executes in 0.3 secs in MySQL 5.0.67 and 3.0 secs in MySQL 5.1.56. Why is it ten times slower in the later version?</p> <p>(Database was exported from 5.0.67 and imported into 5.1.56 so the structure is the same.)</p> <pre><code>SET @num :=0, @current_shop_id := NULL, @current_product_id := NULL; #this query limits the results of the query within it by row number (so that only 10 products get displayed per store) SELECT * FROM ( #this query adds row numbers to the query within it SELECT *, @num := IF( @current_shop_id = shop_id, IF(@current_product_id=product_id,@num,@num+1), 0) AS row_number, @current_shop_id := shop_id AS shop_dummy, @current_product_id := product_id AS product_dummy FROM ( SELECT shop, shops.shop_id AS shop_id, p1.product_id AS product_id FROM products p1 INNER JOIN sex ON sex.product_id=p1.product_id AND sex.sex=0 AND sex.date &gt;= (SUBDATE(NOW(),INTERVAL 7 DAY)) INNER JOIN shops ON shops.shop_id = p1.shop_id ORDER BY shop ) AS testtable ) AS rowed_results WHERE rowed_results.row_number&gt;=0 AND rowed_results.row_number&lt;(0+10) </code></pre> <p>EXPLAIN plan for this query in 5.0.67</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY &lt;derived2&gt; ALL NULL NULL NULL NULL 5433 Using where 2 DERIVED &lt;derived3&gt; ALL NULL NULL NULL NULL 5433 3 DERIVED sex ALL product_id_2,product_id NULL NULL NULL 379571 Using where; Using temporary; Using filesort 3 DERIVED p1 ref PRIMARY,shop_id,shop_id_2,product_id,shop_id_3 product_id 4 mydatabase.sex.product_id 1 3 DERIVED shops eq_ref PRIMARY PRIMARY 4 mydatabase.p1.shop_id 1 </code></pre> <p>EXPLAIN plan for this query in 5.1.56</p> <pre><code>1 PRIMARY &lt;derived2&gt; ALL NULL NULL NULL NULL 491 Using where 2 DERIVED &lt;derived3&gt; ALL NULL NULL NULL NULL 491 3 DERIVED shops ALL PRIMARY NULL NULL NULL 163 Using filesort 3 DERIVED p1 ref PRIMARY,shop_id,shop_id_2,product_id,shop_id_3 shop_id_2 4 mydatabase.shops.shop_id 41 3 DERIVED sex eq_ref product_id_2,product_id product_id_2 5 mydatabase.p1.product_id 1 Using where </code></pre> <p>INDEXES</p> <p>products table for 5.0.67</p> <pre><code>Keyname Type Cardinality Action Field PRIMARY PRIMARY 502437 product_id shop_id UNIQUE 502437 shop_id link title_2 UNIQUE 502437 title image brand INDEX 38649 brand title INDEX 251218 title date INDEX 125609 date shop_id_2 INDEX 87 shop_id product_id INDEX 502437 product_id date shop_id_3 INDEX 125609 shop_id date sale_date INDEX 187 sale_date </code></pre> <p>products table for 5.1.61</p> <pre><code>Action Keyname Type Unique Packed Column Cardinality Collation Null Comment Edit Drop PRIMARY BTREE Yes No product_id 493078 A Edit Drop shop_id BTREE Yes No shop_id 0 A link 493078 A Edit Drop title_2 BTREE Yes No title 0 A image 493078 A Edit Drop brand BTREE No No brand 123269 A YES Edit Drop title BTREE No No title 493078 A Edit Drop date BTREE No No date 41089 A Edit Drop shop_id_2 BTREE No No shop_id 12026 A Edit Drop product_id BTREE No No product_id 493078 A date 493078 A Edit Drop shop_id_3 BTREE No No shop_id 12026 A date 49307 A Edit Drop sale_date BTREE No No sale_date 5940 A </code></pre> <p>shops table for 5.0.67</p> <pre><code> Keyname Type Cardinality Action Field PRIMARY PRIMARY 163 shop_id </code></pre> <p>shops table for 5.161</p> <pre><code>Action Keyname Type Unique Packed Column Cardinality Collation Null Comment Edit Drop PRIMARY BTREE Yes No shop_id 163 A </code></pre> <p>sex table for 5.0.67</p> <pre><code>Keyname Type Cardinality Action Field product_id_2 UNIQUE 506094 product_id sex product_id INDEX 506094 product_id </code></pre> <p>sex table for 5.1.61 </p> <pre><code>Action Keyname Type Unique Packed Column Cardinality Collation Null Comment Edit Drop product_id_2 BTREE Yes No product_id 0 A sex 496732 A Edit Drop product_id BTREE No No product_id 496732 A </code></pre> <p>my.cnf file for MySQL 5.1.61</p> <pre><code>[mysqladmin] user=username [mysqld] basedir=/opt/bitnami/mysql datadir=/opt/bitnami/mysql/data port=3306 socket=/opt/bitnami/mysql/tmp/mysql.sock tmpdir=/opt/bitnami/mysql/tmp character-set-server=UTF8 collation-server=utf8_general_ci max_allowed_packet=16M wait_timeout = 120 long_query_time = 1 log_slow_queries log_queries_not_using_indexes query_cache_limit=2M query_cache_type=1 query_cache_size=8M innodb_additional_mem_pool_size=8M innodb_buffer_pool_size=16M #innodb_log_file_size=128M #tmp_table_size=64M #max_connections = 2500 #max_user_connections = 2500 #innodb_flush_method=O_DIRECT #key_buffer_size=64M [mysqld_safe] mysqld=mysqld.bin [client] default-character-set=UTF8 port=3306 socket=/opt/bitnami/mysql/tmp/mysql.sock [manager] port=3306 socket=/opt/bitnami/mysql/tmp/mysql.sock pid-file=/opt/bitnami/mysql/tmp/manager.pid default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin </code></pre> <p>I noticed if I remove the outer queries for adding the row numbers, the time difference drops to 0.3 secs versus 0.7 secs.</p> <pre><code>SELECT shop, shops.shop_id AS shop_id, p1.product_id AS product_id FROM products p1 INNER JOIN sex ON sex.product_id=p1.product_id AND sex.sex=0 AND sex.date &gt;= (SUBDATE(NOW(),INTERVAL 7 DAY)) INNER JOIN shops ON shops.shop_id = p1.shop_id ORDER BY shop </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjela
    1. USjela
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POwhy is this MySQL query so slow in MySQL 5.1.56?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POwhy is this MySQL query so slow in MySQL 5.1.56?
      1. USLightness Races in Orbit
      1. VTFavorite
    3. VO
      singulars
      1. POwhy is this MySQL query so slow in MySQL 5.1.56?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODid indexes export/import properly/at all?
      singulars
      1. POwhy is this MySQL query so slow in MySQL 5.1.56?
      1. USJohn Dvorak
    2. CORun `EXPLAIN SELECT ...` on both DBs and compare
      singulars
      1. POwhy is this MySQL query so slow in MySQL 5.1.56?
      1. USJohn Dvorak
    3. COAre you using the same `mysql.ini` file?
      singulars
      1. POwhy is this MySQL query so slow in MySQL 5.1.56?
      1. USJocelyn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload