CODEWITHSUNDEEP
mysqlpaginationsql-execution-plan
user3621726
user3621726

Reputation: 15

High cost for a pagination query

I have a pagination query which does range index scan on a large table:

create table t_dummy (
  id int not null auto_increment,
  field1 varchar(255) not null,
  updated_ts timestamp null default null,
  primary key (id),
  key idx_name (updated_ts)

The query looks like this:

select * from t_dummy a
where a.field1 = 'VALUE'
and (a.updated_ts > 'some time' or (a.updated_ts = 'some time' and a.id > x)
order by a.updated_ts, a.id
limit 100

The explain plan show large cost with rows value being very high, however, it is using all the right indexes and the execution seems fast. Can someone please tell whether this means the query is inefficient?

Upvotes: 0

Views: 273

Answers (3)

Rick James
Rick James

Reputation: 142453

As Bill says, Explain cannot be trusted to take LIMIT into account.

The following will confirm that the query is touching only 100 rows:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

The Handler_read% values will probably add up to about 100. There will probably be no Handler_write% values -- they would indicate the creation of a temp table.

A tip: If you use LIMIT 101, you get the 100 rows to show, plus an indication of whether there are more rows. This, with very low cost, avoids having a [Next] button that sometimes brings up a blank page.

My tips on the topic: http://mysql.rjweb.org/doc.php/pagination

Upvotes: 1

ysth
ysth

Reputation: 98398

If the execution is fast enough, don't worry about it. If it is not, consider a (field1,updated_ts) index and/or changing your query to

and a.updated_ts >= 'some time' and (a.updated_ts > 'some time' or a.id > x)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562731

EXPLAIN can be misleading. It can report a high value for rows, despite the fact that MySQL optimizes LIMIT queries to stop once enough rows have been found that satisfy your requested LIMIT (100 in your case).

The problem is, at the time the query does the EXPLAIN, it doesn't necessarily know how many rows it will have to examine to find at least 100 rows that satisfy the conditions in your WHERE clause.

So you can usually ignore the rows field of the EXPLAIN when you have a LIMIT query. It probably won't really have to examine so many rows.

Upvotes: 2

Related Questions