MySQL doesn't use a table index when doing a string search like %string%,
but it MAY use the index when doing something string%. Is there a way you
can change your search to do a "string starts with" instead of a "string
contains" ? This should help with MySQL using the index that you want it to
use.
HTH
See: http://www.mysql.com/doc/M/y/MySQL_indexes.html
This link has some good info that might be of some help :)
HTH
Sam Masiello
Systems Analyst
Chek.Com
(716) 853-1362 x289
[EMAIL PROTECTED]
-----Original Message-----
From: Chai-Hup Chen [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 29, 2001 12:59 PM
To: [EMAIL PROTECTED]
Subject: How to have faster select?
Hi,
I find in our database, select "% string %" is very slow (>1
minute). The database size have only 2M rows, any idea how could I get
faster response? Thanks a lot.
CREATE TABLE Books (
isbn char(10) NOT NULL default '',
Title char(255) NOT NULL default '',
KEY isbn (isbn),
KEY Title (Title)
}
select count(*) from Books -> 2028971
select count(*) from Books where Title like "% cisco %";
+----------+
| count(*) |
+----------+
| 86 |
+----------+
1 row in set (1 min 25.09 sec)
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
