SQL 'ORDER BY' clause slowness

ghz 8months ago ⋅ 55 views 
SELECT pp.productID
FROM productPrices pp
LEFT JOIN products p ON pp.productID = p.productID
WHERE MATCH(p.productName) AGAINST('beef' IN NATURAL LANGUAGE MODE)
    AND pp.storeID IN ('store-4513')
ORDER BY pp.currentPrice LIMIT 10 OFFSET 0;

I have this query that runs incredibly quick without the ORDER BY but once it's added it takes >20 seconds to run. I've added a FULLTEXT index for productName, INDEX for storeID in productPrices, INDEX for currentPrice in productPrices and a few others but that doesn't help. This is what it looks like when adding EXPLAIN. The productPrices table has 1 million rows and it says it's indexing it which could be the issue.

+------+-------------+-------+--------+------------------------------------------------------------+------------------+---------+---------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys                                              | key              | key_len | ref                       | rows | Extra       |
+------+-------------+-------+--------+------------------------------------------------------------+------------------+---------+---------------------------+------+-------------+
|    1 | SIMPLE      | pp    | index  | idx_storeID,idx_storeID_productID,idx_storeID_currentPrice | idx_currentPrice | 5       | NULL                      | 78   | Using where |
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,idx_productID                                      | PRIMARY          | 52      | table.pp.productID | 1    | Using where |
+------+-------------+-------+--------+------------------------------------------------------------+------------------+---------+---------------------------+------+-------------+

I'm running the MariaDB server on a slow server which could be part of the reason but without the ORDER BY it runs in less than 0.2 seconds.

Answers

Adding an ORDER BY clause can significantly affect the performance of a query, especially when operating on large datasets. In your case, the slow performance may be attributed to the sorting operation on the large result set.

Here are a few suggestions to potentially improve the performance of your query:

  1. Optimize Indexes: Ensure that all columns involved in filtering (productName, storeID) and sorting (currentPrice) are properly indexed. Based on the EXPLAIN output, it seems you have indexes on storeID, productID, and currentPrice, which is good.

  2. Index Merge: MariaDB might not be able to use more than one index per table in the query. You could try creating composite indexes that cover multiple columns used in the WHERE clause to allow for index merge optimization.

  3. Query Optimization: Since you're using a FULLTEXT index on productName, consider if there are ways to optimize the query further. For instance, you might try removing unnecessary columns from the SELECT list or using a covering index if possible.

  4. Limit the Result Set: If you only need to retrieve a small subset of rows, consider adding more specific conditions to limit the result set before applying the ORDER BY clause.

  5. Server Configuration: Ensure that your MariaDB server is properly configured and has enough resources allocated to handle the query load efficiently.

  6. Query Cache: Consider enabling query caching if it's not already enabled. This can help reduce the execution time for frequently executed queries.

  7. Analyze the Data Distribution: Analyze the distribution of data in the productPrices table, especially in the currentPrice column. Skewed data distribution can impact query performance.

  8. Database Server Performance: If your server is underpowered, upgrading hardware or optimizing server settings could potentially improve query performance.

By applying these optimizations, you may be able to reduce the execution time of your query with the ORDER BY clause. It's also essential to benchmark the performance after each optimization to evaluate its effectiveness.

#mysql