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.