MySQL Forums
Forum List  »  Performance

Re: The usual - help optimizing create/select match-up for 2-column db
Posted by: KimSeong Loh
Date: October 24, 2004 07:57PM

Maybe you can try this

CREATE theTable (
user_id smallint unsigned,
characteristic_id smallint unsigned,
PRIMARY KEY (user_id, characteristic_id),
UNIQUE KEY (characteristic_id,user_id)
);


Your 2nd query should be faster than the 1st, but the difference is that the 2nd query needs to read 2 indexes and the table rows where the 1st query only reads the 1 index without the table rows.
So, the longer time taken by the 2nd query may due to the additional disk access.
The query I suggested should not require to read the table rows, but have to read 2 indexes.

1st query, have to read the whole index tree (user_id, characteristic_id) and parse it completely.

2nd query, have to read the necessary portion of index (characteristic_id) and the corresponding table rows, then read the corresponding portion of the PRIMARY KEY (user_id, characteristic_id), reading the table rows may cause a lot of random disk access.

My suggested query, have to read the necessary portion of index (characteristic_id,user_id) and the corresponding portion of the PRIMARY KEY index (user_id, characteristic_id).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: The usual - help optimizing create/select match-up for 2-column db
2149
October 24, 2004 07:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.