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.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)Thanks.
keith