Using nested query to improve SELECT performance in MySQL

Question

I want to store multiple MPTT (Modified Preorder Travelsal Trees) in one MySQL table with the following columns: node_id, user_id, rht, lft, value. Single tree is assigned to a single user on the website.

To select tree from specified node for a user I would use:

SELECT * FROM categories 
WHERE user_id = 123 
AND lft > node_lft 
AND rht < node_rht;

I think about using nested query for this function:

SELECT t.* FROM 
 (SELECT * FROM categories WHERE user_id = 123) t
WHERE lft > node_lft
AND rht < node_rht;

Which of the queries is faster when operating on large data (e.g 10000 users, everyone have a single tree with random depth and number of elements) and why?

Gordon Linoff · Accepted Answer · 2016-04-08 20:43:59Z

2

Do not use nested subqueries in the FROM clause in MySQL unless you have a really good reason. MySQL materializes such subqueries. In addition to overhead, it also prevents the use of indexes for joins.

Instead, just define the right index on the table. Based on your query:

categories(user_id, lft, rht)

answered Apr 8, 2016 at 20:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Rick James · Accepted Answer · 2016-04-09 18:52:57Z

Any index starting with user_id will be beneficial for either query. Please provide SHOW CREATE TABLE.

Without the subquery:

INDEX(user_id, lft) (or INDEX(user_id, rht)) will scan perhaps half of the user_id=123 rows; a small improvement over simply INDEX(user_id). Because of the 'range' (lft > nodelft), (user_id, lft, rht), a 3-column index does get past the range on lft; hence is not beneficial.

With the subquery:

Extract all the rows with user_id=123; put into a tmp table.
Scan that tmp table. No index is useful.

The subquery can't be faster. If it is, then it may be that there was a difference in what was cached when you performed the timing tests.

How big is the table? How big is the cache (innodb_buffer_pool_size if InnoDB)? If the table is too huge, then 'lazy lookup' may be warranted.

Do you use the node_id for anything? Is this combo unique: (user_id, lft, rht); if so it could be the PRIMARY KEY. (Accessing via the PK us usually faster than via a secondary key in InnoDB.)

Collectives™ on Stack Overflow

Using nested query to improve SELECT performance in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related