Ad

Using Nested Query To Improve SELECT Performance In MySQL

- 1 answer

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?

Ad

Answer

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)
Ad
source: stackoverflow.com
Ad