Slow results with simple, well-indexed query

[Jesse Sheidlower]

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/my***********...ie.nctu.edu.tw