You could always add an index on company_name + rcd_id. That technically shouldn't help, but I've seen crazier things before.
Donny > -----Original Message----- > From: mos [mailto:[EMAIL PROTECTED] > Sent: Monday, November 15, 2004 4:23 PM > To: [EMAIL PROTECTED] > Subject: RE: Poor Select speed on simple 1 table query > > At 03:32 PM 11/15/2004, Donny Simonton wrote: > >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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
