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
Donny,
Unfortunately I can't. The query must return all rows that *start* with a certain phrase. It's similar to doing a search on a range of values using Between "fra" and "frazzz" but this too is very very slow (324 seconds to return 62k rows). The Like operator will use the index if the wildcard is not used in the first character position. The Explain command shows the index is being used, and the thing that I think is slowing it down is not the Where clause but the reference to Rcd_Id which is not in the index. It's almost like MySQL is returning the results from the index file and then doing a non-indexed table join to the table data to get the Rcd_Id.
Mike
> -----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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
