Cybot wrote:
> Jesse Sheidlower wrote:
>> An example of a query is to get all the words (the cg.cw
>> field) in a particular alphabetical range that have been
>> added in some timespan (the sref.cd field). The cg table
>> has about 3M rows, and the sref table about 70,000; the
>> intervening tables are all indexed on the relevant id
>> fields:
>> mysql> SELECT cg.cw FROM cg,q,cit,sref
>> -> WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id =
>> sref.id
>> -> AND cg.cw BETWEEN 't' AND 'tzzz'
>> -> AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
> move your DATE before cw
> AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
> AND cg.cw BETWEEN 't' AND 'tzzz'
I'ld try a changed table list after FROM: sref, cit, q, cg.
Your query started by selecting a few out of 3M, while some out of 70k still
might be less. Just my rule of thumb: Far faster response keep intermediate
results small by joining in the largest table last.
I prefer to write out all joins ...
SELECT cg.cw FROM sref JOIN cit ON sref.id = cit.sref_id
JOIN q ON cit.id = q.cit_id
JOIN cg ON q.id = cg.q_id
WHERE sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
AND cg.cw BETWEEN 't' AND 'tzzz'
ORDER BY cg.cw
LIMIT 1000,10;
... don't think there is any gain in it on MySQL.
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
