CODEWITHSUNDEEP
mysql
jela
jela

Reputation: 1469

why is this MySQL query so slow in MySQL 5.1.56?

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?

(Database was exported from 5.0.67 and imported into 5.1.56 so the structure is the same.)

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 >= (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>=0 AND
rowed_results.row_number<(0+10)

EXPLAIN plan for this query in 5.0.67

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    5433    Using where
2   DERIVED <derived3>  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

EXPLAIN plan for this query in 5.1.56

1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    491 Using where
2   DERIVED <derived3>  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

INDEXES

products table for 5.0.67

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

products table for 5.1.61

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

shops table for 5.0.67

 Keyname    Type    Cardinality Action  Field
PRIMARY  PRIMARY     163             shop_id

shops table for 5.161

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  shop_id 163 A

sex table for 5.0.67

Keyname Type    Cardinality Action  Field
product_id_2     UNIQUE  506094              product_id
sex
product_id   INDEX   506094              product_id

sex table for 5.1.61 

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

my.cnf file for MySQL 5.1.61

[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

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.

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 >= (SUBDATE(NOW(),INTERVAL 7 DAY)) INNER JOIN
    shops ON
    shops.shop_id = p1.shop_id 

    ORDER BY shop

Upvotes: 2

Views: 700

Answers (2)

John Dvorak
John Dvorak

Reputation: 27287

Some of your indexes in the new database show cardinality 0 while in the old database they show a large value. This probably means that the new database has a wrong idea how efficient the index is. Analyzing the table updates the information about the values that the table holds so that the database knows which index to choose.

Run

ANALYZE TABLE table_name

for every table you have. Alternatively, you can analyze all tables with a single command:

ANALYZE TABLE products, shops, ...

ref.: http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

Upvotes: 1

Gleiemeister 2000
Gleiemeister 2000

Reputation: 729

try putting some USE INDEX (http://dev.mysql.com/doc/refman/5.1/en/index-hints.html) in, and experience with trying to find the right order to use the indexes. On big queries mysql is often very poor to find the right indexes to use.

Upvotes: 0

Related Questions