Jacob Elder wrote:
Thanks to everyone who helped me with this. I settled on breaking it down into area code, exchange and subscriber. This is one of the most generous lists I've ever had the pleasure of begging for help on.
I don't want to deter you from making the changes above, but it's pretty easy to figure out if the key-buffer is too small; it's much less work to try that out first, and then if it doesn't work, making radical changes to your data.
Also, keep in mind that MySQL will use only one index per table in a query. If you break a single row into three columns, and then index the three columns, MySQL will now have to decide which of the three indexes to use. This means that because you are indexing a subset of your data, MySQL will end up doing more work because the index will not be as exact. For example,
SELECT area_code, prefix, postfix FROM phone_numbers WHERE area_code=402 and prefix=232 and postfix=4222;
will force MySQL to pick the index on area_code, prefix, or postfix. Since postfix is the most discriminating index, it will reduce the number of rows down the fastest. Say there are 800 rows with the postfix "4222". It now has to scan those 800 rows to match the area code and prefix. No index will be used there; it's a small scan, but it will add overhead to each search.
If you keep all the data in one column, then the one and only index should be chosen, and it should go straight to the row.
One other thing to consider is the length of your index. For char and varchar indexes, you can tell MySQL how many of the characters you want included in your index.
For example, "CREATE INDEX index_name ON table(column(4));
will only index the first four characters of the column. You may want to specify an index that matches the length of your data (10 characters, I believe). Also, keep in mind that a char is faster than a varchar if you have exactly 10 characters for each row (rather than 7 sometimes, and 10 other times). I am not sure what the default index length is, but it could be something like 32 or 64. Anyone know>?
David
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
