I have a Web application that allows users to search for text in a
potentially complicated form, and then return results. Leaving aside
the issue of the speed of FULLTEXT searching, which I'm discussing
in a separate thread, I'm having a problem with an initial COUNT
query.
When the user enters their search, I first do a COUNT so I can get
the total result size and set up the pager for the usual next/previous
skipping through the results. When the result set is large, this initial
COUNT can be extremely slow; this, for example, is from a question about
words in the letter "M" within the last six months:
mysql> SELECT(cg.cw) AS cwcount 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 LIKE 'm%' AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
+---------+
| cwcount |
+---------+
| 4666 |
+---------+
1 row in set (1 min 11.26 sec)
Everything is indexed here; the EXPLAIN shows:
*************************** 1. row ***************************
table: cg
type: range
possible_keys: q_id,cw
key: cw
key_len: 101
ref: NULL
rows: 147780
Extra: Using where
*************************** 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)
This is on a 1.4GHz PIII server running FreeBSD 4.7 with 1G of RAM.
Needless to say, over a minute for a single user's query is
unacceptably slow; generally after the COUNT, when I'm issuing
LIMITed SELECT queries, things get much better.
Is there any way to improve on this?
Thanks.
Jesse Sheidlower
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
