I've been immersing myself in reading and trying to understand what I can about how keys and indexes work and the flow of a query. Here's an example of one that's not real efficient and I'm not sure if it can be made any more efficient or done some other way to go faster though. I'm guessing a "like '%word%'" query of a varchar(80) is going to sequentially search everything any way you slice it? The purpose of that row is to perform a "pure alpha" search and is just the first and last name fields concatenated with all spaces and punctuation removed. Someone here came up with that idea a long time ago and I'm also wondering if there's not a more efficient way to do that? Some way to form a query that can tell it to ignore spaces and punctuation in text in a field? Hmm... Thanks!
mysql> describe TBL_AllNames; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | InstNum | varchar(8) | | PRI | | | | Year | varchar(4) | | PRI | | | | NameType | char(2) | | PRI | | | | NameClass | char(1) | YES | MUL | NULL | | | NameAP | char(1) | YES | | NULL | | | Ncount | int(11) | | PRI | 0 | | | LastName | varchar(80) | YES | MUL | NULL | | | FirstName | varchar(60) | YES | MUL | NULL | | | TypeofName | varchar(20) | YES | | NULL | | | PAName | varchar(80) | YES | MUL | NULL | | | SoundKeyFirst | varchar(12) | YES | MUL | NULL | | | SoundKeyLast | varchar(12) | YES | MUL | NULL | | | RecDate | varchar(8) | | MUL | | | | InstCode | varchar(10) | | MUL | | | | IndexType | varchar(4) | | | | | | XrefGroup | varchar(8) | | | | | +---------------+-------------+------+-----+---------+-------+ 16 rows in set (0.01 sec) mysql> explain SELECT LastName,FirstName,InstNum,NameType FROM TBL_AllNames WHERE PAName LIKE '%dawn%' ORDER BY RecDate; +--------------+-------+---------------+---------+---------+------+----- ----+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+-------+---------------+---------+---------+------+----- ----+-------------+ | TBL_AllNames | index | NULL | RecDate | 8 | NULL | 6083145 | Using where | +--------------+-------+---------------+---------+---------+------+----- ----+-------------+ 1 row in set (0.00 sec) mysql> SELECT PAName,LastName,FirstName FROM TBL_AllNames WHERE PAName LIKE '%dawn%' ORDER BY RecDate limit 5; +----------------------+-------------------------+-----------+ | PAName | LastName | FirstName | +----------------------+-------------------------+-----------+ | STEELEDAWNCRESTINC | STEELE DAWN CREST INC | | | NEWDAWNAPARTMENTSLTD | NEW DAWN APARTMENTS LTD | | | NEWDAWNAPTSLTD | NEW DAWN APTS LTD | | | LEEDAWNEM | LEE | DAWNE M | | NEWDAWNAPARTMENTSLTD | NEW DAWN APARTMENTS LTD | | +----------------------+-------------------------+-----------+ 5 rows in set (3.12 sec) ^ Without the limit of 5 returned 1200+ hits and took over 6 minutes to run and was probably also cached the second time I ran it with the limit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
