[Solved] Slow sql query - over 15000ms - simple SELECT query, CPU over 100%
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Slow sql query - over 15000ms - simple SELECT query, CPU over 100%

Database type:

I have page with simple sql queries:

Visitor is on page 940 so I need to find products to show on this page:

SELECT * 
FROM items 
WHERE stock = 1 AND hide = 0 
ORDER BY id DESC 
LIMIT 36 
OFFSET 33804

this query takes 15463.242 ms.

Also I need to show filter with manufacturers, sizes and store info for all products in stock:

SELECT MANUFACTURER, sizes, store 
FROM items 
WHERE stock = 1 AND hide = 0

this takes 17996.684 ms.

I don't understand why it takes so much time.

Structure of table:

id  int(11) Auto Increment   
ID_PRODUCT  varchar(200)     
PRODUCT varchar(200)     
DESCRIPTION mediumtext   
URL varchar(300)     
PRICE_VAT   int(7)   
MANUFACTURER    varchar(150)     
CATEGORY    varchar(150)     
IMGSURL varchar(3000)    
catids  varchar(30)  
sizes   varchar(30)  
store   varchar(30)  
stock   int(1)   
hide    int(1)

Table info:

Data size: 327 974 912
Index size: 12 075 008
Free space: 4 194 304
Rows: 310 823

It uses InnoDB and mysql 5.5.5-10.0.29-MariaDB-0+deb8u1.

Can you please help me what is wrong with this queries? Visitors can't wait over 30 seconds. Also the CPU is more than 100% while doing hte query.

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 OFFSET In LIMIT Clause (query line: 9): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
  2. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  3. 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.
Optimal indexes for this query:
ALTER TABLE `items` ADD INDEX `items_idx_stock_hide_id` (`stock`,`hide`,`id`);
ALTER TABLE `items` ADD INDEX `items_idx_id` (`id`);
The optimized query:
SELECT
        * 
    FROM
        items 
    WHERE
        items.stock = 1 
        AND items.hide = 0 
    ORDER BY
        items.id DESC LIMIT 36 OFFSET 33804

Related Articles



* original question posted on StackOverflow here.