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
