Drupal Performance Tips: Avoid DISTINCT in Drupal Views and SQL queries to speed up query execution and improve page load times

On one of the maintenance projects we got we came across a page that used to take around 11 seconds to load. The page was a views listing for users. We were able to speed up the page load times from around 11 seconds to under 3 seconds by taking out just the distinct option used in the view. The following was the select query that was originally generated from the view with the time logged by views in the preview panel.

SELECT DISTINCT users.uid AS uid, users.name AS users_name,
profile_users.pid AS profile_users_pid, users.created AS
users_created, users.access AS users_access, 'profile2' AS
field_data_field_profile_city_profile2_entity_type, 'profile2' AS
field_data_field_profile_state_profile2_entity_type, 'profile2' AS
field_data_field_profile_zip_profile2_entity_type, 'profile2' AS
field_data_field_profile_country_profile2_entity_type, 'advuser:page'
AS view_name
FROM
{users} users
LEFT JOIN {profile} profile_users ON users.uid = profile_users.uid AND
profile_users.type = 'name_and_address'
LEFT JOIN {profile} profile_users_1 ON users.uid = profile_users_1.uid
AND profile_users_1.type = 'email_preferences'
ORDER BY users_created DESC, users_access DESC
LIMIT 100 OFFSET 0

On changing the query settings to not use distinct the query became

SELECT users.uid AS uid, users.name AS users_name, profile_users.pid
AS profile_users_pid, users.created AS users_created, users.access AS
users_access, 'profile2' AS
field_data_field_profile_city_profile2_entity_type, 'profile2' AS
field_data_field_profile_state_profile2_entity_type, 'profile2' AS
field_data_field_profile_zip_profile2_entity_type, 'profile2' AS
field_data_field_profile_country_profile2_entity_type, 'advuser:page'
AS view_name
FROM
{users} users
LEFT JOIN {profile} profile_users ON users.uid = profile_users.uid AND
profile_users.type = 'name_and_address'
LEFT JOIN {profile} profile_users_1 ON users.uid = profile_users_1.uid
AND profile_users_1.type = 'email_preferences'
ORDER BY users_created DESC, users_access DESC
LIMIT 100 OFFSET 0

The comparison in terms of time taken are as follows

With Distinct

Query build time 16.57 ms
Query execute time 10486.89 ms
View render time 212.25 ms

Without Distinct

Query build time 16.98 ms
Query execute time 2255.54 ms
View render time 209.84 ms

So remember - the next time you think of using DISTINCT in your sql query or in your Drupal Views configuration think of the cost you are going to pay in terms of page load times.