* John Berman > I have a single table with 123 columns and currently it holds 49,000 > records [...] > 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 ?
Your approach is correct, just do it... :) 1. Insert a new field in the table, for instance SURNAME_SDX char(5) ALTER TABLE global ADD SURNAME_SDX char(5); <URL: http://www.mysql.com/doc/en/ALTER_TABLE.html > (I think char(5) is ok for soundex with 50K rows, but it depends on the names... you should experiment, and consider using one more or one less character.) 2. Populate it with an UPDATE statement: UPDATE global SET SURNAME_SDX = soundex(SURNAME); <URL: http://www.mysql.com/doc/en/UPDATE.html > 3. Index the new column. There are (at least) two ways to do that, using ALTER TABLE ADD INDEX or CREATE INDEX: ALTER TABLE global ADD INDEX soundex_index (SURNAME_SDX); or CREATE INDEX soundex_index ON global (SURNAME_SDX); <URL: http://www.mysql.com/doc/en/CREATE_INDEX.html > After this you must change your query: ... WHERE ... SURNAME_SDX = soundex('cohen') That should speed things up considerably. If you need even more speed, consider multiple column indexes: <URL: http://www.mysql.com/doc/en/Multiple-column_indexes.html > and normalization: <URL: http://www.mysql.com/portal/sites/item-146.html > > Another question > > > In my select statement use * or actually name the fields required. * is an alias for all columns. When it does what you want, there is no reason not to use it. If you don't want all columns, you have to name those you want. You can also use "tablename.*", usefull when joining multiple tables. HTH, -- Roger --------------------------------------------------------------------- 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
