problem with indexes and temporary/filesort

dannylewin

Hi,

I'm wondering how i can improve my queries to make them run better.

I run NEARLY the same query in 3 different places with only small changes, but it still returns all 700+ rows when i may only need 6 and using LIMIT doesn't change anything.

These are my tables:

CREATE TABLE dress_listing (
dress_id int(6) unsigned NOT NULL AUTO_INCREMENT,
member_id int(6) unsigned NOT NULL,
designer_id tinyint(3) unsigned NOT NULL,
condition_id tinyint(1) unsigned NOT NULL,
age_id tinyint(1) unsigned NOT NULL,
shipping_id tinyint(1) unsigned NOT NULL,
terms_id tinyint(1) unsigned NOT NULL,
dress_title varchar(100) NOT NULL,
dress_desc text NOT NULL,
dress_currency char(3) NOT NULL,
dress_price decimal(6,2) NOT NULL,
dress_old_price decimal(6,2) NOT NULL,
price_euro decimal(6,2) unsigned DEFAULT '0.00',
price_obo tinyint(1) unsigned DEFAULT '0',
dress_reduced tinyint(1) unsigned DEFAULT '0',
dress_sku varchar(50) DEFAULT NULL,
dress_tidy_url varchar(90) NOT NULL,
dress_chest double(4,2) NOT NULL DEFAULT '0.00',
dress_waist double(4,2) NOT NULL DEFAULT '0.00',
dress_drop_waist double(4,2) NOT NULL DEFAULT '0.00',
dress_sleeve double(4,2) NOT NULL DEFAULT '0.00',
dress_skirt double(4,2) NOT NULL DEFAULT '0.00',
dress_across_shoulders double(4,2) NOT NULL DEFAULT '0.00',
dress_bodice_shoulder_waist double(4,2) NOT NULL DEFAULT '0.00',
dress_bodice_neck_waist double(4,2) NOT NULL DEFAULT '0.00',
dress_full_length double(4,2) NOT NULL DEFAULT '0.00',
dress_status char(1) NOT NULL DEFAULT 'P',
dress_added datetime NOT NULL,
dress_updated datetime NOT NULL,
dress_reduced_date datetime DEFAULT NULL,
dress_official tinyint(1) unsigned DEFAULT '0',
discount_id varchar(12) DEFAULT NULL,
use_points tinyint(1) unsigned DEFAULT '0',
is_dress tinyint(1) unsigned DEFAULT '1',
PRIMARY KEY (dress_id),
KEY search1 (dress_status,price_euro),
KEY search2 (dress_status,dress_added),
KEY member (member_id,dress_id),
KEY currency (dress_currency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1738 ;

CREATE TABLE age_ranges (
age_id tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
age_range varchar(6) NOT NULL,
PRIMARY KEY (age_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

CREATE TABLE dress_condition (
condition_id tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
dress_condition varchar(10) NOT NULL,
PRIMARY KEY (condition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

CREATE TABLE dress_designers (
designer_id int(3) unsigned NOT NULL AUTO_INCREMENT,
m_id int(4) unsigned DEFAULT '0',
designer_name varchar(30) NOT NULL,
total_dresses int(3) unsigned NOT NULL DEFAULT '0',
designer_link varchar(80) DEFAULT NULL,
designer_show_info tinyint(1) unsigned DEFAULT '0',
designer_tidy_url varchar(50) DEFAULT NULL,
PRIMARY KEY (designer_id),
KEY total_dresses (total_dresses),
KEY designer_name (designer_name(3)),
KEY tot_name (designer_name(4),total_dresses)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=97 ;

CREATE TABLE dress_images (
image_id int(6) unsigned NOT NULL AUTO_INCREMENT,
dress_id int(6) unsigned NOT NULL DEFAULT '0',
image_file varchar(40) NOT NULL,
is_main tinyint(1) unsigned DEFAULT '0',
PRIMARY KEY (image_id),
KEY dress_id (dress_id,is_main)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5795 ;

CREATE TABLE activity_feed (
activity_id int(10) unsigned NOT NULL AUTO_INCREMENT,
dress_id int(6) NOT NULL,
member_id int(6) NOT NULL,
activity_type tinyint(1) NOT NULL,
activity_date datetime NOT NULL,
PRIMARY KEY (activity_id),
KEY activity_date (activity_date),
KEY dress_id (dress_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1250 ;

CREATE TABLE exchange_rates (
currency char(3) NOT NULL,
rate float unsigned NOT NULL DEFAULT '0',
use char(1) NOT NULL,
symbol char(8) NOT NULL,
updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (currency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE featured_dresses (
featured_id int(4) unsigned NOT NULL AUTO_INCREMENT,
member_id int(6) unsigned DEFAULT NULL,
dress_id int(6) unsigned NOT NULL,
featured_active tinyint(1) unsigned NOT NULL DEFAULT '0',
featured_daily tinyint(1) unsigned DEFAULT '0',
featured_added datetime NOT NULL,
featured_expires date NOT NULL,
PRIMARY KEY (featured_id),
KEY featured_id (featured_id,featured_active),
KEY featured_active (featured_active,featured_expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=432 ;

QUERIES

This in on the homepage to show the 8 most recently added, but MYSQL searches all:

SELECT dress_listing.dress_id,
dress_listing.dress_title as listing_title, 
dress_listing.dress_reduced, 
dress_images.image_file as main_image, 
dress_listing.dress_price as price, 
exchange_rates.rate,
dress_designers.designer_name, 
dress_designers.designer_tidy_url, 
dress_listing.dress_tidy_url as listing_tidy_url
FROM dress_listing 
LEFT JOIN featured_dresses ON dress_listing.dress_id = featured_dresses.dress_id AND featured_dresses.featured_active = '1' 
JOIN exchange_rates ON dress_listing.dress_currency = exchange_rates.currency
JOIN dress_designers ON dress_listing.designer_id = dress_designers.designer_id 
LEFT JOIN dress_images ON dress_listing.dress_id = dress_images.dress_id AND is_main = '1' 
WHERE dress_listing.dress_status = 'A'
LIMIT 8

My main search page query

SELECT dress_listing.dress_id,
dress_listing.dress_title as listing_title,
dress_images.image_file as main_image,
dress_listing.dress_added,
CASE WHEN featured_dresses.dress_id IS NULL then '0' ELSE '1' END as order_it,
dress_listing.dress_price as price,
dress_listing.dress_old_price as old_price,
dress_listing.price_euro,
dress_listing.price_obo,
exchange_rates.rate,
dress_listing.dress_reduced,
dress_designers.designer_name,
dress_designers.designer_tidy_url,
dress_listing.dress_tidy_url as listing_tidy_url

FROM dress_listing
LEFT JOIN featured_dresses ON dress_listing.dress_id = featured_dresses.dress_id AND featured_dresses.featured_active = '1'
JOIN exchange_rates ON dress_listing.dress_currency = exchange_rates.currency
JOIN dress_designers ON dress_listing.designer_id = dress_designers.designer_id
LEFT JOIN dress_images ON dress_listing.dress_id = dress_images.dress_id AND is_main = '1'
JOIN members_table ON dress_listing.member_id = members_table.member_id
WHERE dress_listing.dress_status = 'A'
AND dress_listing.price_euro between '0' AND '5000'
ORDER BY dress_listing.dress_added DESC

The odd thing is that it uses the index, and avoids full scans and filesort. BUT if i remove the following join it doesnt. Why has this become so important that removing it makes MYSQL use temporary tables and filesort!?

LEFT JOIN featured_dresses ON dress_listing.dress_id = featured_dresses.dress_id AND featured_dresses.featured_active = '1'

Also this is supposed to get the 6 most recent "Activity" rows, but returns 1200+:

SELECT 
activity_feed.activity_type,
activity_feed.activity_date,
dress_listing.dress_tidy_url,
dress_listing.dress_id,
dress_listing.dress_title,
dress_listing.dress_price as price, 
dress_listing.dress_old_price as old_price, 
dress_listing.dress_currency,
exchange_rates.rate,
age_ranges.age_range, 
dress_designers.designer_name, 
dress_images.image_file
FROM activity_feed
JOIN dress_listing ON activity_feed.dress_id = dress_listing.dress_id
JOIN exchange_rates ON dress_listing.dress_currency = exchange_rates.currency
JOIN dress_designers ON dress_listing.designer_id = dress_designers.designer_id 
JOIN age_ranges ON dress_listing.age_id = age_ranges.age_id 
LEFT JOIN dress_images ON activity_feed.dress_id = dress_images.dress_id AND is_main = 1
ORDER BY activity_feed.activity_date DESC
LIMIT 6

I removed all currency calculations from the queries and moved them to PHP and to combat the sorting by price added price_euro to the "dress_listing" table which is calculated and inserted when the record is.

Any suggestions for improving these (ESPECIALLY the Activity Feed query) would be greatly appreciated.

dannylewin

This is the EXPLAIN for the Activity table:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE exchange_rates ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
1 SIMPLE dress_listing ref PRIMARY,currency currency 9 dannyle_dresses.exchange_rates.currency 142
1 SIMPLE dress_designers eq_ref PRIMARY PRIMARY 4 dannyle_dresses.dress_listing.designer_id 1 Using where
1 SIMPLE age_ranges eq_ref PRIMARY PRIMARY 1 dannyle_dresses.dress_listing.age_id 1
1 SIMPLE activity_feed ref dress_id dress_id 4 dannyle_dresses.dress_listing.dress_id 1 Using where
1 SIMPLE dress_images ref dress_id dress_id 6 dannyle_dresses.activity_feed.dress_id,const 1

And the Search:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE dress_listing ref search1,search2,member,currency search2 3 const 721 Using where
1 SIMPLE dress_designers eq_ref PRIMARY PRIMARY 4 dannyle_dresses.dress_listing.designer_id 1 Using where
1 SIMPLE dress_images ref dress_id dress_id 6 dannyle_dresses.dress_listing.dress_id,const 1
1 SIMPLE featured_dresses ref featured_active featured_active 1 const 4
1 SIMPLE exchange_rates eq_ref PRIMARY PRIMARY 9 dannyle_dresses.dress_listing.dress_currency 1
1 SIMPLE members_table eq_ref PRIMARY,member_id PRIMARY 4 dannyle_dresses.dress_listing.member_id 1 Using index

dannylewin

I tried changing the activity query to something like:

SELECT 
a.activity_type,
a.activity_date,
l.*
FROM activity_feed as a
JOIN (
SELECT 
dress_listing.dress_tidy_url,
dress_listing.dress_id,
dress_listing.dress_title,
dress_listing.dress_price as price, 
dress_listing.dress_old_price as old_price, 
dress_listing.dress_currency,
exchange_rates.rate,
age_ranges.age_range, 
dress_designers.designer_name, 
dress_images.image_file
FROM dress_listing 
JOIN exchange_rates ON dress_listing.dress_currency = exchange_rates.currency
JOIN dress_designers ON dress_listing.designer_id = dress_designers.designer_id 
JOIN age_ranges ON dress_listing.age_id = age_ranges.age_id 
LEFT JOIN dress_images ON dress_listing.dress_id = dress_images.dress_id AND is_main = 1
) as l ON l.dress_id = a.dress_id 
GROUP BY a.activity_id
ORDER BY a.activity_date DESC
LIMIT 6

But the explain then looks like:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 849 Using temporary; Using filesort
1 PRIMARY a ref dress_id dress_id 4 l.dress_id 1 Using where
2 DERIVED dress_listing ALL NULL NULL NULL NULL 849
2 DERIVED age_ranges eq_ref PRIMARY PRIMARY 1 dannyle_dresses.dress_listing.age_id 1
2 DERIVED dress_designers eq_ref PRIMARY PRIMARY 4 dannyle_dresses.dress_listing.designer_id 1 Using where
2 DERIVED dress_images ref dress_id dress_id 6 dannyle_dresses.dress_listing.dress_id 1
2 DERIVED exchange_rates ALL PRIMARY NULL NULL NULL 6 Using where; Using join buffer

johanafm

dannylewin;11038571 wrote:
I run NEARLY the same query in 3 different places with only small changes

"small" may be a matter of perspective.

dannylewin;11038571 wrote:
, but it still returns all 700+ rows when i may only need 6 and using LIMIT doesn't change anything.

You should probably define "it" and "returns", considering that LIMIT most definitely changes how many rows you get in your result set. If you really do get a result set containing anything other than 6 rows, you are not looking at the correct query(ies)

But if you are referring to the number of joined rows while the result set is being built… you are most likely correct.

Consider this:

1. I give you 19 each of the numerals 1 to 9.
2. I ask you to create all permutations of length 2
[you create these permutations]
-> Give me 9 of those permutations (LIMIT 9).

EXPLAIN: how many permutations did you create?

Then consider this:

1. I give you 19 each of the numerals 1 to 9.
2. I ask you to create all permutations of length 2
   WHERE the second numeral is 3
[you work]
-> Give me any 9 of those permutations (LIMIT 9).

EXPLAIN: how many permutations did you create?

dannylewin;11038571 wrote:
The odd thing is that it uses the index, and avoids full scans and filesort. BUT if i remove the following join it doesnt. Why has this become so important that removing it makes MYSQL use temporary tables and filesort!?
LEFT JOIN featured_dresses ON dress_listing.dress_id = featured_dresses.dress_id AND featured_dresses.featured_active = '1'

I doubt that line actually does anything at all in the posted query. It is a left join and no result from the joined table is used anywhere else.
But assuming that you have modified your order by before posting and that it should contain order by "order_it DESC" (featured dresses before others), which I base on the presence of this field

CASE WHEN featured_dresses.dress_id IS NULL then '0' ELSE '1' END as order_it,

then assuming there are more than 6 featured dresses included in the end result, only the featured dresses would need to be sorted after price, rather than the entire pre-limit result set. I do not know if it's possible for the planner to realize that there will be at least 6 such rows (after all tables are joined) so that it can start with featured_dresses and only join on those rows. On the other hand, if the planner is not able to deduce this, but you know that each row in featured_dresses will exist in the end result, then you could pre-select feature-dress data, check if there are 6+ rows and if so join only on these rows. But also note that if I'm correct, you'd lose this advantage as the number of rows in featured-dresses goes beyond some decent threshold. Try inserting more rows and you should see filesort reappearing.

Another thing that could improve this regardless of feature presence is to add index on price. If you have no index, then all rows will need to be sorted before 6 can be selected. Also note that if that works, you could add currently_featured into the dress-listing table, and simply keep the featured-dresses table for a history of what dresses have been featured and when that was. With this approach, you could keep your index in dress_listing on (featured, price) and this should hopefully improve things. I usually go by hunches and resort to trial and error when working with these things. Not having the actual data to work on means I have no way of verifying wether my guesses will work or not…

Next up, the activity thingy. Do you want dresses from the last 6 activity entries? If so, why are you NOT joining directly? I.e. why the derived table in which you have no relation to the 6 posts.

SELECT ...
FROM activity a
JOIN dress d ON a.dress_id = d.dress_id
…

When it comes to exchange rates: How many are there? Are they needed? All of them? For all clients?
Usually I'd personally be interested in one - whichever happens to be used in my own country.

NogDog

I think johanafm already addressed this, but I was just getting ready to reply when I saw he beat me to it, but the GROUP BY and ORDER BY have to happen first on the whole result set before the LIMIT gets applied, so at least in this case, the LIMIT won't affect how many table rows get processed.