On Thu, Aug 21, 2003 at 06:01:31PM +0200, Cybot wrote: > Jesse Sheidlower wrote: > > >I'm struggling with speed issues on some queries that > >I would have expected to be relatively fast. Perhaps > >even more frustratingly, when I've tried to break > >these down into their components, they still execute > >very slowly. I've looked over all the relevant suggestions > >for optimization and so forth, and there's nothing I can > >tell that I'm missing. > > > >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' > > cause "sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)" should be faster > than "cg.cw BETWEEN 't' AND 'tzzz'" and so the total rows are already > limited when "cg.cw BETWEEN 't' AND 'tzzz'" will be executed
I assume that the optimizer would take care of this, but in any case I gave it a try and it made no difference. > also you can try an index with a length of 2 or 3 over cg.cw, this will > result in smaller index and possible speed up things I also tried this (the current index is 25 characters on a 100-character field), and if anything it made things slower. Anyone have any other ideas or analysis? Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
