As first step, try to optimize table with help of OPTIMIZE TABLE MyTable command.
Any progress? Best regards, Mikhail. ----- Original Message ----- From: "David BORDAS" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 27, 2002 12:34 PM Subject: Slow select query, need some clues to speed it up please ... > Hi all, > > I've discovered that we have a select query that blocked all others query to > this table. > > 99% of query used indexs, no join ... > But this one is a problem : > SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE > Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR > Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; > > I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE > '%John')' part is the problem. > > Table desc : > CREATE TABLE MyTable ( > Field1 int(10) unsigned NOT NULL auto_increment, > Field2 int(10) unsigned NOT NULL default '0', > Field3 varchar(50) NOT NULL default '', > Field4 varchar(50) NOT NULL default '', > Field5 text NOT NULL, > Field6 bigint(20) NOT NULL default '0', > Field7 int(10) unsigned NOT NULL default '0', > PRIMARY KEY (Field1), > KEY ReplyTo_Numero (Field2,Field1), > KEY indexF (Field7,Field2,Field6) > ) TYPE=MyISAM PACK_KEYS=1; > > select count(*) from MyTabe ; > 4381036 > > Table is growing about 10 000 record a day ... > > So, what can i do to have better performance ? > Create a big index for Field3,Field4, Field5 ? > > Not a very good idea i think because it'll be a very very big index and > Field 3-5 are varchar or text ... > But why not ... > > Perhaps doing 3 select, one with Field3, one with Field4 and the other with > Field5 and then doing some code in the app for joining the 3 select results. > > Or did i miss something in the select syntax that can help me ? > > Thanks > David > > > --------------------------------------------------------------------- > 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
