Please help to optimize performance.
Query is "select * from addresses order by address limit 1000 offset
90000" runs very slowly.
Field 'address' is varchar(255), indexed.
Here are 'explain' result and 'describe addresses' result:

mysql> explain select * from addresses order by address;
+----+-------------+-----------+------+---------------+------+---------
+------+-
-------+----------------+
rows | Extra |
+----+-------------+-----------+------+---------------+------+---------
+------+-
-------+----------------+
+----+-------------+-----------+------+---------------+------+---------
+------+-
-------+----------------+
1 row in set (0.00 sec)

mysql> describe addresses;
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.20 sec)


--------------------------------------+
`address` varchar(255) DEFAULT NULL,
`header` int(11) DEFAULT NULL,
`kind` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `header__idx` (`header`),
KEY `idx_addresses` (`address`),
CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`header`) REFERENCES
`headers` (`id
`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=101825 DEFAULT CHARSET=utf8 |
+-----------
+-------------------------------------------------------------------

SergeyPo <ffvantaa (AT) gmail (DOT) com> wrote:

What is "very slowly"? Are you aware that ORDER BY ... LIMIT 90000,1000
has to read and sort 91000 rows anyway?

Totally expected. You have 103000 rows in that table. The query has to
read all of them. No use for any index. The table scan + sort is very
probably faster than 103000 random row reads from that table.

Of course, if your query would be

SELECT address FROM addresses ORDER BY address

then MySQL could use a covering index read (don't read any row from
the table, but get all data from the index only). Since this is InnoDB,
you could even use SELECT address, id ...

But have you tried the EXPLAIN for SELECT ... LIMIT 90000,1000? Now
it should use a range scan on the index, skipping the first 90000
index records and then 1000 random row reads.


It /could/ be worth trying to rewrite the query like so:

SELECT * FROM addresses t1
JOIN
(SELECT id FROM addresses ORDER BY address LIMIT 90000,1000) t2
ON t1.id=t2.id

(or alternatively as subquery with IN)

Here the subquery could use the covering index and the outer would
use the PK to refer result rows.


XL

On 18 май, 20:21, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:
XL, Thank you for your reply!

mysql> explain select * from addresses order by address limit 1000
offset 90000;

+----+-------------+-----------+------+---------------+------+---------
+------+--------+----------------+
+------+--------+----------------+
+------+--------+----------------+

still no index is being used. If I run this select in console, it took
4.92 sec.


If, as you suggested, I explicitly select some fields, it seems
better:

mysql> explain select address, id from addresses order by address
limit 1000 off
set 90000;
+----+-------------+-----------+-------+---------------+---------------
+---------+------+-------+-------------+
+---------+------+-------+-------------+
+---------+------+-------+-------------+

This one runs 1.75 sec.

This is great progress but for my app I need all fields in the result
set. When I try to add any other field to SELECT, it stops using the
index.

I tried the following query:
mysql> select * from addresses where addresses.id in (select id from
addresses order by address) limit 1000 offset 90000;
+-------+--------------------+--------+-----------------------+
1000 rows in set (0.36 sec)
but this gives wrong result: note all those '000000...' values in
'address' column, actually they should contain text. Can you point me
what's wrong in my query? - it's really fast and definitely uses an
index.

At last, I tried your JOIN query, and with slight changes it worked
fast:

mysql> select * from addresses t1 join (select id from addresses order
by address) t2 on t1.id = t2.id limit 1000 offset 90000;

1000 rows in set (0.33 sec)

Thank you very much, and hope this post helps someone else to read
data from simple sorted table

Sergey

SergeyPo <ffvantaa (AT) gmail (DOT) com> wrote:
Hmm. I wonder if that is an optimizer bug.

Yep. The index on (address) is no longer a covering index then.

http://dom.as/2007/01/26/mysql-cover...x-performance/
http://www.xaprb.com/blog/2006/07/04...optimizations/

<snip>

Yes. Because the query is wrong. Should have been

select * from addresses where addresses.id in
(select id from addresses order by address limit 1000 offset 90000);

notice the position of the closing bracket?


XL