Re: The usual - help optimizing create/select match-up for 2-column db

[KimSeong Loh]

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).