Modern web applications often use pagination to enhance user experience and manage large datasets more efficiently. However, when dealing with tables containing millions of records, poorly optimized pagination queries can lead to serious performance issues. At this point, creating indexes not only for WHERE clause columns but also for columns used in ORDER BY and pagination operations can significantly improve query performance. In this blog post, we'll explore how an ORDER BY ... LIMIT query behaves in MySQL both with and without an index, comparing execution plan outputs using EXPLAIN ANALYZE. We'll also touch on similar optimization techniques used in other popular database systems like PostgreSQL. If you're ready, let’s dive into the inner workings of database performance!
Modern web pages uses pagination to show query results. Using this method also optimizes query performance if the query returns large rows.Most of time index creates for the columns in where clause of sql queries to optimize sql queries. In addition to this you may use indexes for pagination and optimizing orders.
For example a pagination query likes this;
select * from product_orders order by order_datelimit 100
select * from product_orders order by order_datelimit 100
select * from product_orders order by order_datelimit 100
product_orders tables have 5000000 records. Without index mysql engine must read all table rows sort by order_date then limit 100 records which is very inefficient shown in execution plan in MYSQL db. cost is 535090.
Execution Plan Without Index
explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s) (cost=535090 rows=100)
-> Sort: product_orders.order_date, limit input to 100 row(s) per chunk (cost=535090 rows=4.82e+6)
-> Table scan on product_orders (cost=535090 rows=4.82e+6)explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s) (cost=535090 rows=100)
-> Sort: product_orders.order_date, limit input to 100 row(s) per chunk (cost=535090 rows=4.82e+6)
-> Table scan on product_orders (cost=535090 rows=4.82e+6)explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s) (cost=535090 rows=100)
-> Sort: product_orders.order_date, limit input to 100 row(s) per chunk (cost=535090 rows=4.82e+6)
-> Table scan on product_orders (cost=535090 rows=4.82e+6)
After creating index on order_date and looking execution plan cost is 1.1 which is 500000 times less then original query plan.
Execution Plan With Index
explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s) (cost=1.1 rows=100)
-> Index scan on product_orders using ix_order_date_product_orders (cost=1.1 rows=100)
explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s) (cost=1.1 rows=100)
-> Index scan on product_orders using ix_order_date_product_orders (cost=1.1 rows=100)
explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s) (cost=1.1 rows=100)
-> Index scan on product_orders using ix_order_date_product_orders (cost=1.1 rows=100)
