Re: Composite index

[Julian Assange]

Hi Rick,

Thank you for your message.

Create:

CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cookie` int(20) unsigned NOT NULL,
.....
KEY `idcookie` (`id`,`cookie`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=828194 DEFAULT CHARSET=utf8

The table has more columns but they are either ints or varchars not used in this query.


The purpose of the query is to authenticate users browsing a site in order to show content. Their cookie contains their id and a number 'cookie', hence the result should almost always find 1 row.

EXPLAIN

mysql> explain select id,cookie from users where id = 828191 and cookie = 1656382;
+----+-------------+------------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users| const | PRIMARY,idcookie | PRIMARY | 4 | const | 1 | |
+----+-------------+------------+-------+---------------------------+---------+---------+-------+------+-------+

For the benchmark the query was made of multiple comma separated select queries with different id/cookie pairs for which a row exists in the table so that to replicate multiple users all requesting to be authenticated.

It is obviously very important to maximise performance of this single query since it will be so heavily used across the site and like I said on my original post I would have thought the composite index would be sufficient to solve the query.