Re: Slow results with simple, well-indexed query

[Cybot]

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

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

or you add another field (cg.cw_short) with first 2 (or 3 or even 1) 
letter of cg.cw and add an index on this and add to your query:

AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
AND cg.cw_short BETWEEN 't' AND 'tz'
AND cg.cw BETWEEN 't' AND 'tzzz'
pls post here if this hepls out or not

    -> 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



--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]