Hi all, I seem to have difficulties efficiently using multiple column unique indexes. I notice major performance differences in the following example. Is this a known problem, or am I not doing the right thing?
The following scenario takes for me 0.76 seconds: CREATE TABLE test(d_id INT, t_id INT, value VARCHAR(255)); CREATE UNIQUE INDEX test_idx on test(d_id, t_id); SELECT * FROM test WHERE d_id < 500 AND t_id < 500; This takes nearly 1 minute if I try to use MERGE tables (the index would still remain UNIQUE) The data in the test table has the following features: Both d_id and t_id values occure 2-3000 times (it't probably not good for the b-tree) It seems like first the query evaluates (d_id < 500) and slowly comes up with 500,000 results. Finally (t_id < 500) dramatically reduces the result set to 200. If I set PACK_KEYS = 1, and even rebuild the indexes, nothing really changes. I don't know how multiple column keys are handled, are they treated as one primary key or are they looked up separately, butif I combine the d_id and t_id keys in a new table to produce a primary key e.g. as (d_id*10,000,000+t_id) everything is fast (0.01 sec), (but wierd). Could anyone recommend a better solution in this case? Thanks for any comments! Charlie _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. --------------------------------------------------------------------- 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
