Using Nested Query To Improve SELECT Performance In MySQL
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?
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)
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key