Link to home
Start Free TrialLog in
Avatar of bitt3n
bitt3n

asked on

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)

Open in new window

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	 

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window


shops table for 5.0.67
Keyname	Type	Cardinality	Action	Field
    PRIMARY	 PRIMARY	 163 			 shop_id

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

Identical hardware?
Avatar of bitt3n
bitt3n

ASKER

the machine running MySQL 5.1.56 appears to be faster. selecting all rows in the products table:

Showing rows 0 - 29 ( 493,078 total, Query took 0.0001 sec)

Open in new window

versus
Showing rows 0 - 29 (502,504 total, Query took 0.0949 sec)

Open in new window

Can you list the machine specs especially CPU and ram?
Avatar of bitt3n

ASKER

The MySQL 5.1.56 machine is a bitnami virtual server hosted on Amazon Web Services. The MySQL 5.0.67 machine is a shared hosting account. I can ask the host what the specs of the machine are if that's useful, but they'll definitely be low.

other semi-complex queries do not run appreciably differently on the two machines (assuming the shared host isn't under load, which I imagine it was in the above example). certainly not enough to explain a 10x difference.
If it has less ram and all of a sudden the query can't fit in memory it will definitely be 10x slower as it has to go to disk. Just a thought.
Avatar of bitt3n

ASKER

Specs for the bitnami server:

A micro instance, or micro server, is a cloud server with 638 MB of RAM that allows for up to 2 Elastic Compute Units or ECUs. An ECU is the equivalent of a 1.0-1.2 GHz 2007 Opteron or 2007 Xeon processor. Micro instances are good for low throughput applications or development.

EDIT: the shared hosting is only 128 MB and 0.5CPU (presumably that's not much more than 1.0 GHz)

This query might be a better benchmark. it runs in 0.20 secs on the bitnami server (later MySQL) and 0.21 secs on the earlier version.

SET @num :=0, @current_shop_id := NULL, @current_product_id := NULL;

SELECT * FROM (

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 fav6.favorites_count AS #COALESCE(fav6.favorites_count,0) not needed here to zero NULL values because this is the favorites page, so there will be a favorites count for every product displayed
	favorites_count, c2.comments_count AS
	comments_count, shop, shops.shop_id AS
	shop_id, products.product_id AS
	product_id, brand, title, price, image, image_width, image_height, fav4.closeted AS
	session_user_closeted, fav1.date AS
	user_date, fav1.sex AS
	sex, fav2.date AS
	circle_date, fav2.closeted AS
	circle_closeted, users.username AS
	circle_username,
	
	#circle_vote determines whether at least one friend voted on the product. (this datum makes it easy to highlight products on which friends have voted (eg with red border around product image))
	((SELECT COUNT(*) FROM
	favorites fav3, friends f2 WHERE
	fav3.product_id=products.product_id AND
	f2.fan_id=22 AND
	f2.star_id=fav3.user_id AND
	fav3.current=1)>0) AS
	circle_vote
	
	FROM products LEFT JOIN
	
	# this LEFT JOIN associates favorites_count table (adds up the scores for all the products in the favorites table)
	(
		SELECT fav5.product_id AS product_id, SUM(CASE 
		WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
		WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
		ELSE 0
		END) AS favorites_count
		FROM favorites fav5
		GROUP BY fav5.product_id 
	
	) AS fav6 ON products.product_id=fav6.product_id LEFT JOIN 
	# associates comments_count table
	(
		SELECT c1.product_id AS product_id,
		COUNT( 
		CASE 
		WHEN b.read_date IS NOT NULL 
		AND c1.post_date > b.read_date
		THEN c1.comment_id
		WHEN b.read_date IS NULL 
		THEN c1.comment_id
		ELSE NULL 
		END ) AS comments_count
		FROM comments c1
		LEFT JOIN bookmarks b ON ( b.product_id = c1.product_id
		AND b.user_id = 22 ) 
		GROUP BY c1.product_id 
	) AS c2 ON c2.product_id=products.product_id INNER JOIN
	
	#limit results to products favorited by the user whose closet is being shown
	favorites fav1 ON
	fav1.product_id=products.product_id AND 	
	fav1.current=1 AND
	fav1.closeted=1 AND 
		fav1.user_id=22 INNER JOIN shops ON
	shops.shop_id = products.shop_id INNER JOIN favorites fav2 ON
	
	# get a record for every user in the user's circle who has voted on a product favorited by the user (circle = friends of user AND user)
	products.product_id = fav2.product_id AND
	(fav2.user_id IN (SELECT
	star_id FROM
	friends f1 WHERE
	f1.fan_id=22) OR
	fav2.user_id=22) AND
	fav2.current=1 INNER JOIN
	users ON
	fav2.user_id=users.user_id
	
	# determine whether the session user has closeted/killed the relevant item. Because the session user might be different from the user whose favorites the session user is viewing, the query limits the results to products favorited by the viewed user in the WHERE clause, and with this LEFT JOIN determines whether the session user favorited the products selected by the WHERE clause.
	
	LEFT JOIN favorites fav4 ON
	fav4.user_id=22 AND
	fav4.current=1 AND
	fav4.product_id=products.product_id
	
	#this query displays results in the order of the date at which the user voted on the product (not the date from the products table)
	
	#GROUP BY the combination of (product_id, circle_username) because we want one row per product_id/circle_username combo. grouping by just product_id would throw away circle_username values if more than one circle user favorited the product
	GROUP BY product_id, circle_username
	ORDER BY shops.shop ASC, user_date DESC, product_id DESC, circle_closeted DESC, circle_date DESC
	
	) AS test_table
	
	) AS rowed_results WHERE rowed_results.row_number>=0 AND rowed_results.row_number<(0+7)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of bitt3n
bitt3n

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bitt3n

ASKER

sdf