> Did you read about optimizing joins? Yes I've read the manuals & docs. I see in the manual a section entitled "Optimising LEFT JOIN and RIGHT JOIN" but my JOIN in question is neither LEFT or RIGHT. Can you please point me at some specific URLs ? thanks, - Sam. Werner Stuerenburg <[EMAIL PROTECTED]> wrote: Did you read about optimizing joins? It is discussed in detail in the manual. Recently, we had a similar discussion here (thread Left Join very sloooowwww..) where it turned out that all the tricks in the manual applied. You can analyze your query and get detailed info on where and how to change your indexes, syntax etc. S A schrieb am Dienstag, 24. Juli 2001, 01:08:31: > 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/ -- Herzlich Werner Stuerenburg _________________________________________________ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de --------------------------------- Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
