CODEWITHSUNDEEP
mysqlsqlpaginationquery-optimization
Irfan.gwb
Irfan.gwb

Reputation: 708

Optimizing pagination query with Seek Method

This seems to be a very common question but did not get the solution for all the cases.

I have searched lots of articles to optimize the pagination query in MYSQL, I found the Seek Method as the best solution.

But in every example of seek method, I found that order by clause on either number or date type fields, What if we are ordering by a varchar type column(eg: first_name) which may contain blank and null values too, I tried (first_name, id) < (null, 15) AND (first_name, id) < ('', 15) which show uncertain result.

Please suggest how can we handle these cases too in seek method.

Upvotes: 2

Views: 4550

Answers (3)

Saurabh Patil
Saurabh Patil

Reputation: 4462

Along with the usage of NULL there is another issue that is not yet pointed out by any answer.

You can't use that syntax with MySQL. Example: (first_name, id) < ('Alex', 15)

This is called “row values” syntax. Even if you don't use NULL and give a proper value, MySQL understands it but isn't able to use index in a proper way to produce the desired sort order and hence you aren't seeing results you expect.

Even though the row values syntax is part of the SQL standard, only a few databases support it. SQL Server 2017 does not support row values at all. The Oracle database supports row values in principle, but cannot apply range operators on them (ORA-01796). MySQL evaluates row value expressions correctly but cannot use them as access predicate during an index access. DB2 (only LUW, since 10.1) and PostgreSQL (since 8.4), however, have a proper support of row value predicates and uses them to access the index if there is a corresponding index available.

Good resource on this topic: https://use-the-index-luke.com/sql/partial-results/fetch-next-page

Upvotes: 0

Rick James
Rick James

Reputation: 142453

    WHERE (first_name, id) < ('', 15)

Is nifty syntax but performance sucks! The optimizer does not know how to use any index to help with such. You must turn it into a complex AND and OR expression. And you must have INDEX(first_name, id) in that order.

As for the expression, etc, the details are spelled out here.

PS, don't use first_name < NULL; NULL compared to anything is always FALSE. For example:

mysql> SELECT 'Rick' < NULL, 'Rick' > NULL, 'Rick' = NULL, 'Rick' != NULL;
+---------------+---------------+---------------+----------------+
| 'Rick' < NULL | 'Rick' > NULL | 'Rick' = NULL | 'Rick' != NULL |
+---------------+---------------+---------------+----------------+
|          NULL |          NULL |          NULL |           NULL |
+---------------+---------------+---------------+----------------+

(And NULL works like FALSE when tested in a WHERE.)

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

It doesn't matter which types of columns you use to order the rows, as long as they are "sortable" by the database. This includes pretty much all numbers, varchars, dates/times, etc.

The common exception are heavy data fields such as BLOB, CLOB, BINARY, etc. This list is very database-specific, so check which data types can participate in an ORDER BY in your database.

Also, please note that to use the Seek Method correctly the list of columns you use to order must produce a unique key. Otherwise, the pagination may work "funny".

Upvotes: 2

Related Questions