Page 1 of 1

mysql, indexes

Posted: Wed Dec 11, 2002 3:05 pm
by kcomer
I am doing some testing/optimizing of our mysql server. I have a table, setup with an index that works correctly, sometimes. Here is the table structure.

Code: Select all

CREATE TABLE `tblReferralInfo` (
  `id` int(10) unsigned NOT NULL default '0',
  `refID` int(10) unsigned NOT NULL auto_increment,
  `refFname` varchar(35) NOT NULL default '',
  `refLname` varchar(35) NOT NULL default '',
  `refAddress` varchar(100) NOT NULL default '',
  `refCity` varchar(50) NOT NULL default '',
  `refState` char(2) NOT NULL default '',
  `refZip` varchar(10) NOT NULL default '',
  `refPhone` varchar(10) NOT NULL default '',
  `refMesPhone` varchar(10) NOT NULL default '',
  `refCounty` varchar(50) NOT NULL default '',
  `refSource` varchar(100) default NULL,
  `refOther` varchar(50) default NULL,
  `refMethod` varchar(25) default NULL,
  `refReasons` varchar(100) default NULL,
  `refFrontdesk` varchar(50) default NULL,
  `refSite` int(10) default NULL,
  `refDate` datetime default NULL,
  `refNotes` longtext,
  `refEntryDate` datetime default NULL,
  `refEntryUser` int(10) unsigned default NULL,
  PRIMARY KEY  (`refID`),
  UNIQUE KEY `hohID` (`refID`),
  KEY `Search` (`refID`,`refLname`,`refFname`)
) TYPE=MyISAM COMMENT='WSOS in house referral info.
Here are the queries/explaines to better explain my problem.

Code: Select all

mysql> EXPLAIN SELECT refID FROM tblReferralInfo WHERE refLname LIKE '%smith%' ORDER BY refFname, refLname;
+-----------------+-------+---------------+--------+---------+------+--------+-----------------------------------------+
| table           | type  | possible_keys | key    | key_len | ref  | rows   | Extra                                   |
+-----------------+-------+---------------+--------+---------+------+--------+-----------------------------------------+
| tblReferralInfo | index | NULL          | Search |      74 | NULL | 206986 | where used; Using index; Using filesort |
+-----------------+-------+---------------+--------+---------+------+--------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT refID FROM tblReferralInfo WHERE refLname = 'smith' ORDER BY refFname, refLname;
+-----------------+------+---------------+-------+---------+-------+------+------------+
| table           | type | possible_keys | key   | key_len | ref   | rows | Extra      |
+-----------------+------+---------------+-------+---------+-------+------+------------+
| tblReferralInfo | ref  | Lname         | Lname |      35 | const |    1 | where used |
+-----------------+------+---------------+-------+---------+-------+------+------------+
1 row in set (0.00 sec)
As you can see the "= 'smith'" is much better than "LIKE '%SMITH%'". How can I speed up my query, I have tried a few ways, with no success. I am using MySQL 3.23.49. Any help would be great.

Thanks.

keith

Posted: Thu Dec 12, 2002 8:04 am
by Rob the R
What query exactly are you wanting to speed up? The "like" version? How long is your query running now, and how many rows is it working on?

Also, the second explain plan appears to refer to an index called "LName", but I don't see that defined anywhere in your table structure. Is it just a non-unique index on "refLName"?

Posted: Thu Dec 12, 2002 10:49 am
by kcomer
Not sure what I did there. I poked around in the mysql doc for awhile and finally found where it says using LIKE and not = will cuase every record to be looked at. Thanks for responding though. I'm having strange results using datetime fields and indexes now though. I have over 750,000 records in the table. It uses the index until the result set is over 155,000 records. Wierd. I'll just keep plugging away at it i guess.