I have standard search functionality on my site where a user can see NN items per
screen of a much longer list of search results. I've found that MySQL returns small
result sets out of a list of hits faster when queried in 2 parts than queried once. Am
I doing something wrong ?
I have a fairly standard join query on a few tables with about 20,000 rows.
SELECT g.poster_id, g.poster_name, p.file_name,
p.file_size, p.post_subject, p.post_id,
DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension
FROM users_to_files u, posts p, posters g
WHERE p.poster_id = g.poster_id AND
p.post_id = u.post_id AND u.user_id = $folder_owner_id AND u.folder_id =
$src_folder
ORDER_BY g.poster_name, p.post_subject
LIMIT 0,10
When I do a SELECT on that join to get just 10 rows using an ORDER BY and a LIMIT 0,10
it takes about 2 seconds per set of 10.
As an experiment I broke up this query into 2 parts & it only takes about 0.2 seconds
or less per set of 10.
#1 The first query gets just the post_id's that I care about.
SELECT p.post_id
FROM posts p, posters g $POSTS_TO_GROUPS
WHERE p.poster_id = g.poster_id
AND p.file_size > 0
ORDER BY g.poster_name, p.post_subject
#2 Then I do the joined query on all the columns on just the small list of post_ids to
act as a LIMIT.
SELECT g.poster_id, g.poster_name, p.file_name,
p.file_size, p.post_subject
DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension
FROM posts p, posters g
WHERE p.post_id IN ($post_ids_in) AND p.poster_id = g.poster_id
ORDER BY g.poster_name, p.post_subject
Shouldn't MySQL be as fast or faster to do this join in the 1 query than the 2 queries
?
Does the MySQL optimizer not recognize that only the few rows specified by the LIMIT
actually need to be retrieved ?
If so is there a way to help MySQL optimize the join ?
Displaying a page full portion at a time of a long list of results is a very common
web thing for MySQL to do so hopefully I'm just doing something wrong.
thanks,
- Sam.
---------------------------------
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
