List:General Discussion« Previous MessageNext Message »
From:Adam Wolff Date:May 10 2006 2:38am
Subject:slow query
View as plain text  
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 | Abbott_Aaron@stripped |       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 | Abbott_Benny@stripped |       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 | Abbott_Jimmie@stripped |       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
Thread
slow queryAdam Wolff10 May
  • Re: slow queryDan Buettner10 May
    • Re: slow queryAdam Wolff10 May
      • Re: slow queryDan Buettner10 May