Hi
I have a single table with 123 columns and currently it holds 49,000
records
My ISP is running MySql v: 3.23.38
I use MySql Front to access the table
And I currently have a freetext index on the surname column.
Running a query like
SELECT SURNAME, GIVENNAMES, ageatdeath, gender, dode, mounrnedby,
plotlocation FROM global WHERE PROPOSEDRELEASE <= 2002 and MATCH
(SURNAME) AGAINST ('cohen') and uniqueref ='9' order by givennames
Brings back the result in an acceptable time , ie I ran I 5 times and
the results come back in the following times:
1.16 Secs
1.14 Secs
1.14 Secs
1.20 Secs
1.63 Secs
So the above seems fine.
My Soundex search using the following dramatically changes the response
times, understandably as I'm doing it on the fly.
SELECT SURNAME, GIVENNAMES, ageatdeath, gender, dode, mounrnedby,
plotlocation FROM global WHERE PROPOSEDRELEASE <= 2002 and
soundex(surname) = soundex('cohen') and uniqueref ='9' order by
givennames
12.67 Secs
10.88 Secs
11.39 Secs
12.44 Secs
17.72 Secs
I believe I need to insert a soundex field and pre populate it and then
create an index on the soundex field - If my approach is correct help on
how to do this would be appreciated ?
Another question
In my select statement use * or actually name the fields required.
Regards
John Berman
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
