[Solved] MySQL LIMIT optimization issue
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

MySQL LIMIT optimization issue

Database type:

I have the following MySQL query that I'm looking to make the LIMIT faster as it's running VERY slow. SQL_CALC_FOUND_ROWS equals about 114000 rows.

SELECT SQL_CALC_FOUND_ROWS PStD.ProductID FROM ProductStoreDef PStD
    JOIN ProductSummary PS ON PStD.ProductID = PS.ProductID 
    JOIN MasterVendor MV ON MV.VendorID = PStD.MasterVendorID 
WHERE 
    PStD.SKUStatus = 'A' AND 
    MV.isActive = 1 AND 
    PStD.MasterCategoryID = 66 AND 
    PStD.CustomerPrice > 0
ORDER BY PStD.VendorName, PS.VendorPartNumber 
LIMIT 100000,50

The following is the EXPLAIN results

+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+-----------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                   | key             | key_len | ref                     | rows | Extra                                        |
+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+-----------------+---------+-------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | MV    | ALL    | PRIMARY,isActive,VendorID                                                                       | NULL            | NULL    | NULL                    | 2126 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | PStD  | ref    | PRIMARY,MasterVendorID,MasterCategoryID,SKUStatus,CustomerPrice,MasterVendCatID,ProdStoreStatus | MasterVendCatID | 8       | ecomm.MV.VendorID,const |   94 | Using where                                  | 
|  1 | SIMPLE      | PS    | eq_ref | PRIMARY                                                                                         | PRIMARY         | 4       | ecomm.PStD.ProductID    |    1 |                                              | 
+----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+-----------------+---------+-------------------------+------+----------------------------------------------+

Any advice would be appreciated.

Update: Solved this issue by creating a separate table that pre-calculates the sort order making the site run about 500x to 1000x faster.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid using SQL_CALC_FOUND_ROWS (query line: 2): Including SQL_CALC_FOUND_ROWS statements tend to slow down queries significantly as it doesn't scale well. It's recommended to split this query to two: a data selection query and a counting query.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Sort and Limit Before Joining (modified query below): In cases where the joins aren't filtering any rows, it's possible to sort and limit the amount of rows using a subquery in the FROM clause, before applying the joins to all other tables.
Optimal indexes for this query:
ALTER TABLE `MasterVendor` ADD INDEX `mastervendor_idx_isactive` (`isActive`);
ALTER TABLE `ProductStoreDef` ADD INDEX `productstoredef_idx_skustatus_mastercate_customerpr` (`SKUStatus`,`MasterCategoryID`,`CustomerPrice`);
ALTER TABLE `ProductSummary` ADD INDEX `productsummary_idx_productid` (`ProductID`);
The optimized query:
SELECT
        SQL_CALC_FOUND_ROWS pstd_productid 
    FROM
        (SELECT
            PStD.ProductID AS pstd_productid,
            PStD.MasterVendorID AS PStD_MasterVendorID 
        FROM
            ProductStoreDef PStD 
        JOIN
            ProductSummary PS 
                ON PStD.ProductID = PS.ProductID 
        WHERE
            PStD.SKUStatus = 'A' 
            AND PStD.MasterCategoryID = 66 
            AND PStD.CustomerPrice > 0 
        ORDER BY
            PStD.VendorName,
            PS.VendorPartNumber LIMIT 50) PStD 
    JOIN
        MasterVendor MV 
            ON MV.VendorID = PStD.PStD_MasterVendorID 
    WHERE
        1 = 1 
        AND MV.isActive = 1 
        AND 1 = 1 
        AND 1 = 1 LIMIT 50

Related Articles



* original question posted on StackOverflow here.