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=arch...@jab.org
