slow query issue

SeenGee

Hi,

I'm getting issues with slow queries being generated by a table in my database for my app, this query is consistently generating an entry in the slow query log:

# Query_time: 2  Lock_time: 0  Rows_sent: 27  Rows_examined: 958
SELECT `p_id` FROM `products` LEFT JOIN `products_images` ON (`p_id` = `pi_pid`) WHERE `pi_image_mime` IS NULL AND `p_archived` = '0' GROUP BY `p_id`;

an explain query generates this:

1  	SIMPLE  	ls_products  	ref  	p_archived  	p_archived  	1  	const  	866  	Using where; Using temporary; Using filesort
1 	SIMPLE 	ls_products_images 	ref 	pi_pid 	pi_pid 	4 	shop.ls_products.p_id 	12 	Using where; Not exists

and a desc generates this:

  	 	
                pi_id  	int(11)  	NO  	PRI  	NULL  	auto_increment
	 	pi_pid 	int(11) 	NO 	MUL 	0 	 
	 	pi_label 	varchar(255) 	NO 	  	  	 
	 	pi_primary_image 	tinyint(1) 	NO 	  	0 	 
	 	pi_sort_order 	int(11) 	NO 	  	0 	 
		pi_micro_blob 	mediumblob 	NO 	  	  	 
	 	pi_thumb_blob 	mediumblob 	NO 	  	  	 
	 	pi_large_blob 	mediumblob 	NO 	  	  	 
	 	pi_zoom_blob 	mediumblob 	NO 	  	  	 
	 	pi_image_mime 	varchar(25) 	NO 	  	  	 
	 	pi_date_added 	datetime 	NO 	  	0000-00-00 00:00:00 	 
	 	pi_filename 	varchar(50) 	NO 	  	  	 
	 	pi_image_label 	varchar(255) 	NO

Any ideas ?

Piranha

Have you indexed the fields you use for joins and using in where and group by?

SeenGee

Piranha;10885726 wrote:
Have you indexed the fields you use for joins and using in where and group by?

yes, all thats been done.

Piranha

In what tables are the following columns?

pi_image_mime
p_archived

SeenGee

i don't know if this is a larger issue with the specific images table as i'm also seeing entries for what appears to be a fully optimized query like this:

# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 1
use user_shop;
SELECT `pi_id` FROM `products_images` WHERE `pi_pid` = '809' AND `pi_primary_image` = '1';

n.b. there are individual indexes on pi_pid and pi_primary_image

pi_image_mime is in the products_images table and p_archived is in the products table, i can provide more info as required.

Piranha

If you get the same problem with that query I guess it is something else. Do you use locks on the table for insert/update/delete? Do you have indexes on the other columns, especially the blob columns? How many rows do you have in the image table?

SeenGee

no we dont use locks, we do have other indexes in the products table but none on the blobs themselves in the images table. We have noticed this issue in images table ranging from 2000 to 10000 rows

Piranha

I have a couple of more ideas, but none that I really believe causes the problem. It could be that the database have to much load, or that it is to slow compared to the load.

SeenGee

could you see any value in moving the blobs out of the products_images table to another one ?

Piranha

I don't think so, mainly because the actual blobs are not stored in the table. But again, it might be worth a try.

I guess that the blobs is pictures? In that case it is usually a bad idea to store pictures like that, better to store them on disk and a URL in the database. Any security handling can be done in PHP instead of the database.