MySQL Forums
Forum List  »  MyISAM

Re: Composite index
Posted by: Julian Assange
Date: August 28, 2016 06:18AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
1877
August 26, 2016 05:44PM
891
August 27, 2016 01:55PM
Re: Composite index
898
August 28, 2016 06:18AM
1112
August 29, 2016 11:34PM
951
August 30, 2016 03:16AM
885
August 31, 2016 04:48PM


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.