mysql, indexes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kcomer
Forum Contributor
Posts: 108
Joined: Tue Aug 27, 2002 8:50 am

mysql, indexes

Post 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
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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"?
kcomer
Forum Contributor
Posts: 108
Joined: Tue Aug 27, 2002 8:50 am

Post 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.
Post Reply