If you are running MySQL 5, try moving the WHERE condition into the JOIN condition, which is really where you want the filter since it's part of the join.
SELECT me.id, me.quotation_id, quotation.qt, part.id, part.cit FROM cwGroup me JOIN quotation ON ( quotation.id = me.quotation_id AND quotation.id IN (107037, 304650, 508795, 712723, 1054653)) JOIN part ON ( part.id = quotation.part_id ) That may or may not help, check if the explain changes. Brent Baisley On Mon, Jan 26, 2009 at 6:16 AM, Jesse Sheidlower <jes...@panix.com> wrote: > > I have an app that joins results from a MySQL query with the > results of a lookup against an external search engine, which > returns its results in the form of primary-key id's of one of > the tables in my database. I handle this by adding these > results with an IN query. (My impression had been that this is > faster than a long chain of OR's.) > > In the simplest case, if I'm _only_ searching against these > results, the query will look something like this (I've > removed some columns from the SELECT list for readability): > > SELECT me.id, me.quotation_id, quotation.qt, part.id, part.cit > FROM cwGroup me > JOIN quotation ON ( > quotation.id = me.quotation_id ) > JOIN part ON ( part.id = quotation.part_id ) > WHERE ( quotation.id IN (107037, 304650, 508795, 712723, 1054653)) > > When I ran this on a query that generated a moderate number of > results (over 1000, but not millions), it took MySQL 26 > seconds to reply on my dev box. > > Can someone suggest what I can look at to speed this up? The > section of the manual that talked about optimizing range > queries spent a lot of time explaining how they work but very > little on how to speed them up. The EXPLAIN didn't really > help--only one column got a lot of results, and it's not clear > to me why MySQL would take 26 seconds to fetch 1214 records. > > The EXPLAIN looks like this: > > ------------------- > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: me > type: range > possible_keys: quotation_id > key: quotation_id > key_len: 4 > ref: NULL > rows: 1214 > Extra: Using where > *************************** 2. row *************************** > id: 1 > select_type: SIMPLE > table: quotation > type: eq_ref > possible_keys: PRIMARY,part_id > key: PRIMARY > key_len: 4 > ref: rqs_incs.me.quotation_id > rows: 1 > Extra: > *************************** 3. row *************************** > id: 1 > select_type: SIMPLE > table: part > type: eq_ref > possible_keys: PRIMARY > key: PRIMARY > key_len: 4 > ref: rqs_incs.quotation.part_id > rows: 1 > --------------- > > Thanks very much. > > Jesse Sheidlower > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
