Blog/ How to optimize a Drupal View so that it uses INNER JOIN instead of LEFT JOIN

In a recent project, we needed to show a list of 5 most recently updated users. To do this, we added a Unix timestamp field “User Updated” to the user object and created a simple view with required fields and ordered by User Updated field in descending order. Here is how the view looks like.

The problem was that the site had over 80,000 users and it took over 1.10 seconds to execute this view’s SQL query every time.

SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, users.mail AS users_mail, field_data_field_user_updated.field_user_updated_value AS field_data_field_user_updated_field_user_updated_value, 'user' AS field_data_field_first_name_user_entity_type, 'user' AS field_data_field_last_name_user_entity_type, 'user' AS field_data_field_twitter_id_user_entity_type
FROM 
users users
LEFT JOIN field_data_field_user_updated field_data_field_user_updated ON users.uid = field_data_field_user_updated.entity_id AND (field_data_field_user_updated.entity_type = 'user' AND field_data_field_user_updated.deleted = '0')
WHERE (( (users.status <> '0') ))
ORDER BY field_data_field_user_updated_field_user_updated_value DESC
LIMIT 5 OFFSET 0

alter table `users` add index `status_picture_name_mail` (`status`, `picture`, `name`, `mail`);

Start with field_data_field_user_updated table:

MySQL starts with users table because the view creates a left join. It can not omit any entry in the users table even if there is no corresponding entry in the field_data_field_user_updated table. But we have more information about our application than MySQL does. We know that there will be at least 5 out of 80,000 users who have been updated at least once and hence will have an entry in the field_data_field_user_updated table. So filtering on field_data_field_user_updated table first makes more sense. This effectively turns LEFT JOIN into INNER JOIN. If this were an INNER JOIN, MySQL will apply its optimizations and will use the field_data_field_user_updated table first. We confirmed this by replacing LEFT JOIN with INNER JOIN in the MySQL command line. The new query becomes:

SELECT sql_no_cache users.uid AS uid, users.picture AS users_picture, users.name AS users_name, users.mail AS users_mail, field_data_field_user_updated.field_user_updated_value AS field_data_field_user_updated_field_user_updated_value, 'user' AS field_data_field_first_name_user_entity_type, 'user' AS field_data_field_last_name_user_entity_type, 'user' AS field_data_field_twitter_id_user_entity_type 
FROM  users users 
INNER JOIN field_data_field_user_updated field_data_field_user_updated 
ON users.uid = field_data_field_user_updated.entity_id AND (field_data_field_user_updated.entity_type = 'user' AND field_data_field_user_updated.deleted = '0') WHERE (( (users.status = 1) )) 
ORDER BY field_data_field_user_updated_field_user_updated_value DESC 
LIMIT 5 OFFSET 0;

Here is the output of the EXPLAIN command on this query:

You will notice that now MySQL uses field_data_field_user_updated first.

The next step is how to structure the View so that it uses an INNER JOIN instead of LEFT JOIN. Unfortunately with the fields, Views always uses LEFT JOIN so this was not possible. But we could give enough information via the View to the MySQL so that it uses field_data_field_user_updated table first. To do this, we added a NOT EMPTY filter on the User Updated field in the view. 

This change adds an additional WHERE clause in the SQL query. Note that LEFT JOIN does not change to INNER JOIN. The new SQL query is:

SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, users.mail AS users_mail, field_data_field_user_updated.field_user_updated_value AS field_data_field_user_updated_field_user_updated_value, 'user' AS field_data_field_first_name_user_entity_type, 'user' AS field_data_field_last_name_user_entity_type, 'user' AS field_data_field_twitter_id_user_entity_type
FROM {users} users
LEFT JOIN {field_data_field_user_updated} field_data_field_user_updated ON users.uid = field_data_field_user_updated.entity_id AND (field_data_field_user_updated.entity_type = 'user' AND field_data_field_user_updated.deleted = '0')
WHERE (( (users.status <> '0') AND (field_data_field_user_updated.field_user_updated_value IS NOT NULL ) ))
ORDER BY field_data_field_user_updated_field_user_updated_value DESC
LIMIT 5 OFFSET 0

Because of this condition, MySQL considers LEFT JOIN and INNER JOIN to be equivalent and effectively treats this as an INNER JOIN. As a result, field_data_field_user_updated table is first used to find 5 most recently updated uids and then corresponding fields are loaded from the user table. This change reduced the time execution time from about 1 second to about 0.13 seconds.

If you have any insights or have another way to optimize such queries, feel free to write a comment below.