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]
