Mos, Personally, I never use like for anything. I would add a fulltext index myself and call it a day. But that's me.
Donny > -----Original Message----- > From: mos [mailto:[EMAIL PROTECTED] > Sent: Monday, November 15, 2004 2:40 PM > To: [EMAIL PROTECTED] > Subject: Poor Select speed on simple 1 table query > > It doesn't get any simpler than this. :) > > The Select statement takes way too long to complete. > > select rcd_id, company_name from company where company_name like "fra%" > 12357 rows fetched (86.08 seconds) > > However if it returns just the column value from the index, it is quite > fast: > select company_name from company where company_name like 'fra%' > 12357 rows fetched ( 0.14 sec) > > So by referencing a column (Rcd_Id or Cust_Name) from the data file, it > becomes 600x slower compared to just referencing the value from the index > by itself namely Company_Name. > > I've run Analyze on the table, I've even repaired it and rebuilt the index > with no increase in speed. > > The table has 10 million rows in it. > > CREATE TABLE `company` ( > `Rcd_Id` int(4) NOT NULL auto_increment, > `Company_Name` char(30) NOT NULL default '', > `Cust_Name` char(15) default NULL, > PRIMARY KEY (`Rcd_Id`), > KEY `CompanyName_Index` (`Company_Name`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > > mysql> explain select rcd_id, company_name from company where company_name > like 'fra%'; > +----+-------------+---------+-------+-------------------+---------------- > ---+---------+------+-------+-------------+ > | id | select_type | table | type | possible_keys | > key | key_len | ref | rows | Extra | > +----+-------------+---------+-------+-------------------+---------------- > ---+---------+------+-------+-------------+ > | 1 | SIMPLE | company | range | CompanyName_Index | > CompanyName_Index | 30 | NULL | 10505 | Using where | > +----+-------------+---------+-------+-------------------+---------------- > ---+---------+------+-------+-------------+ > > > mysql> explain select company_name from company where company_name like > 'gre%'; > +----+-------------+---------+-------+-------------------+---------------- > ---+---------+------+-------+--------------------------+ > | id | select_type | table | type | possible_keys | > key | key_len | ref | rows | Extra | > +----+-------------+---------+-------+-------------------+---------------- > ---+---------+------+-------+--------------------------+ > | 1 | SIMPLE | company | range | CompanyName_Index | > CompanyName_Index | 30 | NULL | 10505 | Using where; Using index | > +----+-------------+---------+-------+-------------------+---------------- > ---+---------+------+-------+--------------------------+ > > > So is a 600x slower query typical of queries that reference the data > portion of the table compared to queries that reference just the indexed > columns? > Is there any way to speed it up? > > TIA > > Mike > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
