How to better optimize this mySQL query with subqueries

Question

I am having some issues optimizing a mySQL query that I have been using sub-queries on to try and get the previous and next IDs based off the ID in the query. When I use the EXPLAIN function in mySQL it shows that one of my sub-queries is running through 690k+ rows, which is what I am guessing is affecting the performance.

I have tried to limit the sub-queries to return just just one result and used a MIN and MAX in the sub-queries, but both of those options still returned large sets of rows.

SELECT c.*, (SELECT id FROM cards WHERE id > '733290' ORDER BY id ASC LIMIT 1) AS 'prev', (SELECT id FROM cards WHERE id < '733290' ORDER BY id DESC LIMIT 1) AS 'next' FROM cards c LEFT JOIN albums a ON (a.id = cs.album_id) WHERE c.id = '733290'

In the explain this was the results:

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra

5
SUBQUERY
cards
range
PRIMARY,user_id
PRIMARY
4
NULL
696177
Using where

4
SUBQUERY
cards
range
PRIMARY,user_id
PRIMARY
4
NULL
4
Using where

Edit: Here are the definitions for the cards table:

Keyname    Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
PRIMARY BTREE   Yes No  id  696183  A   No  
user_id BTREE   No  No  user_id 5438    A   No  
album   BTREE   No  No  album   258 A   No

May we see the table definition for the cards table? We need to see this in order to be able to suggest some potential optimizations. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 24, 2019 at 17:17

Bill Karwin · Accepted Answer · 2019-01-24 17:51:53Z

1

Here's a try:

SELECT c.*, cp.id AS `prev`, cn.id AS `next`
FROM cards c
LEFT JOIN cards cp ON c.id < cp.id
LEFT JOIN cards cpp ON c.id < cpp.id AND cp.id > cpp.id
LEFT JOIN cards cn ON c.id > cn.id
LEFT JOIN cards cnn ON c.id > cnn.id AND cn.id < cnn.id
WHERE c.id = 733290
 AND cpp.id IS NULL
 AND cnn.id IS NULL

Explanation: Join c to cp, which has a greater id, but also make sure the set of rows cpp with an id between c.id and cp.id is an empty set. That is, the outer join must return NULL.

Do the same for cn.

edited Jan 24, 2019 at 17:51

answered Jan 24, 2019 at 17:25

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tim Biegeleisen Over a year ago

This assumes that id is a monotonically increasing column.

Bill Karwin Over a year ago

Yes it does. Do you think that would be unusual?

Tim Biegeleisen Over a year ago

I guess I was thinking defensively that this was a general lead/lag problem, where that might not be the case. +1 to answer, which might work here.

zeropsi Over a year ago

I am getting null returns for next and prev using this query. Oh, and yes, the prev/next are reversed because the data is shown in DESC order.

Bill Karwin Over a year ago

I realized my first try was obviously wrong. It could never return the prev and next rows because of the WHERE clause condition. I've replaced it with a different answer.

|

Rick James · Accepted Answer · 2019-01-26 03:40:14Z

EXPLAIN provides bogus information, especially when LIMIT is involved.

Your query is optimal and will not really touch more than 1 row in each direction.

(And I have not figured out what Bill is trying to achieve!)

To get a more reliable count of number of rows touched, do this:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler_read%';

For a similar query (on a table with 3.1M rows), I got

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 3     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

Add up the numbers (and get 3); that will be exactly(?) how many rows were touched. 3 seems correct for your query.

Collectives™ on Stack Overflow

How to better optimize this mySQL query with subqueries

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related