What is the value of "sort_buffer_size", may be you could increase the value for having faster ORDER BY (all in memory intead of using temporary file on disk).
Marc. -----Message d'origine----- De : Jesse Sheidlower [mailto:[EMAIL PROTECTED] Envoyé : jeudi 21 août 2003 17:34 À : [EMAIL PROTECTED] Objet : Slow results with simple, well-indexed query 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) -> ORDER BY cg.cw -> LIMIT 1000,10; +---------------+ | cw | +---------------+ | teeny-pop | | teeter | | teetery | | teeth-grating | | Teflon | | teflon | | teflon | | teflon | | teflubenzuron | | Tejano | +---------------+ 10 rows in set (7.30 sec) ----- That's just too slow; yet an EXPLAIN doesn't make things easy for me to see what's wrong: ----- mysql> EXPLAIN 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) -> ORDER BY cg.cw -> LIMIT 1000,10\G *************************** 1. row *************************** table: cg type: range possible_keys: q_id,cw key: cw key_len: 26 ref: NULL rows: 170982 Extra: Using where; Using filesort *************************** 2. row *************************** table: q type: eq_ref possible_keys: PRIMARY,cit_id key: PRIMARY key_len: 4 ref: cg.q_id rows: 1 Extra: *************************** 3. row *************************** table: cit type: eq_ref possible_keys: PRIMARY,sref_id key: PRIMARY key_len: 4 ref: q.cit_id rows: 1 Extra: *************************** 4. row *************************** table: sref type: eq_ref possible_keys: PRIMARY,cd key: PRIMARY key_len: 4 ref: cit.sref_id rows: 1 Extra: Using where 4 rows in set (0.00 sec) ----- Executing just the search on the word table, with no joins to the table with the dates, is still slow: ----- mysql> SELECT cw -> FROM cg -> WHERE cw BETWEEN 's' AND 'szzz' -> ORDER BY cw -> LIMIT 3000,5; +---------------------+ | cw | +---------------------+ | sacrifice hit | | sacrifice play | | sacrifice the earth | | sacrifice throw | | sacrifice to | +---------------------+ 5 rows in set (5.80 sec) ----- and has a similar EXPLAIN: ----- mysql> EXPLAIN SELECT cw FROM cg WHERE cw BETWEEN 's' AND 'szzz' ORDER BY cw LIMIT 3000,5\G *************************** 1. row *************************** table: cg type: range possible_keys: cw key: cw key_len: 26 ref: NULL rows: 318244 Extra: Using where; Using filesort 1 row in set (0.00 sec) ----- Of course cw is indexed. Is there anything I can to do improve queries of this nature? There are more complicated queries from this database, but the big slowdown always seems to be when one of the possibilities (e.g. all words in 'S') is large; the other limitations don't improve things. Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
