StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMAX(ID) with IN() comparison function performance
text
Body
copied!<p>I have stumbled upon a performance issue with this query. I've stared at this problem for a long time now scratching my head. This query was actually pretty fast at one point, but once data grew, it became slower and slower. The 'Posts' table has +5 million rows, the 'Items' table has +6000 rows. These tables are growing constantly on a daily basis.</p> <pre><code>SELECT Posts.itemID, Items.itemName, Items.itemImage, Items.guid, Posts.price, Posts.quantity, Posts.date, Games.name, Items.profit FROM Items INNER JOIN Posts ON Items.itemID=Posts.itemID INNER JOIN Games ON Posts.gameID=Games.gameID WHERE Posts.postID IN (SELECT MAX(postID) FROM Posts GROUP BY itemID) AND Posts.gameID=:gameID AND Posts.price BETWEEN :price_min AND :price_max AND Posts.quantity BETWEEN :quant_min AND :quant_max AND Items.profit BETWEEN :profit_min AND :profit_max ORDER BY Items.profit DESC LIMIT 0, 20 </code></pre> <p>In the code I've split up the query and sub query into two. Together they were performing slower. This was all good and well, until the data in both the Posts and Items started growing. The 'where' statements that I've put in ** get concatenate depending on what filters are set.</p> <p>Here's the EXPLAIN that I get. (This is the query without the sub query) <a href="https://docs.google.com/file/d/0B1jxMdMfC35VeDBEbnJISmNGb3c/edit?usp=sharing" rel="nofollow">https://docs.google.com/file/d/0B1jxMdMfC35VeDBEbnJISmNGb3c/edit?usp=sharing</a></p> <p><strong>SHOW INDEX FROM Posts</strong>:</p> <pre><code>+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Posts | 0 | PRIMARY | 1 | postID | A | 5890249 | NULL | NULL | | BTREE | | | | Posts | 1 | itemID | 1 | itemID | A | 16453 | NULL | NULL | YES | BTREE | | | | Posts | 1 | gameID | 1 | gameID | A | 18 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ </code></pre> <p><strong>SHOW INDEX FROM Items</strong>;</p> <pre><code>+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Items | 0 | PRIMARY | 1 | itemID | A | 6452 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ </code></pre> <p><strong>SHOW INDEX FROM Games;</strong></p> <pre><code> +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Games | 0 | PRIMARY | 1 | gameID | A | 2487 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ </code></pre> <p>Is there anyway I can make this query faster? Do you guys have any advice? Is there a better way of writing this query? All help is appreciated.</p> <p>EXPLAIN Proposed Query:</p> <pre><code> +----+-------------+------------+--------+-----------------------+---------+---------+----------------------------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-----------------------+---------+---------+----------------------------+---------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 19 | Using temporary; Using filesort | | 1 | PRIMARY | p | eq_ref | PRIMARY,itemID,gameID | PRIMARY | 4 | q.postID | 1 | | | 1 | PRIMARY | i | eq_ref | PRIMARY | PRIMARY | 2 | db323245342342345.p.itemID | 1 | Using where | | 1 | PRIMARY | g | eq_ref | PRIMARY | PRIMARY | 4 | db323245342342345.p.gameID | 1 | Using where | | 2 | DERIVED | p | ref | itemID,gameID | gameID | 2 | | 2945124 | Using where; Using temporary; Using filesort | | 2 | DERIVED | i | eq_ref | PRIMARY | PRIMARY | 2 | db323245342342345.p.itemID | 1 | Using where | +----+-------------+------------+--------+-----------------------+---------+---------+----------------------------+---------+----------------------------------------------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload