I have a very simple table that looks like this:
CREATE TABLE `contacts` (
`id` int(11) NOT NULL auto_increment,
`fullname` varchar(100) default NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`,`fullname`),
CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
ENGINE=InnoDB DEFAULT CHARSET=utf8
It's a bit of a lopsided table in that of the 1,000,100 records in the db,
1,000,000 of them belong to user_id 1. But I wouldn't expect this to
skew my results.
I am writing a little paging server that retrieves pages of data using
LIMIT and OFFSET.
I'm really surprised by how slowly my queries are running on a
relatively fast desktop machine. Records near the top of the list are
fine:
mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname
LIMIT 1 OFFSET 0;
+--------+--------------+-----------------------------+---------+----------+
| id | fullname | email | user_id | nickname |
+--------+--------------+-----------------------------+---------+----------+
| 371543 | Aaron Abbott | [EMAIL PROTECTED] | 1 | aaronab |
+--------+--------------+-----------------------------+---------+----------+
1 row in set (0.03 sec)
But as I move down the list, queries run slower and slower:
mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname
LIMIT 1 OFFSET 100000;
+--------+--------------+-----------------------------+---------+----------+
| id | fullname | email | user_id | nickname |
+--------+--------------+-----------------------------+---------+----------+
| 726543 | Benny Abbott | [EMAIL PROTECTED] | 1 | bennyab |
+--------+--------------+-----------------------------+---------+----------+
1 row in set (2.94 sec)
mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname
LIMIT 1 OFFSET 500000;
+--------+---------------+------------------------------+---------+----------+
| id | fullname | email | user_id | nickname |
+--------+---------------+------------------------------+---------+----------+
| 309543 | Jimmie Abbott | [EMAIL PROTECTED] | 1 | jimmieab |
+--------+---------------+------------------------------+---------+----------+
1 row in set (12.75 sec)
EXPLAIN says:
+----+-------------+----------+------+-------------------+-----------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+----------+------+-------------------+-----------+---------+-------+--------+-------------+
| 1 | SIMPLE | contacts | ref | user_id,user_id_2 | user_id_2 |
4 | const | 506222 | Using where |
+----+-------------+----------+------+-------------------+-----------+---------+-------+--------+-------------+
In other words, it *is* using an index for this query. Anyone have any
advice for me?
Thanks,
Adam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
