Make optimised and quick SQL queries

There are number of rules to follow to make optimized quires. Unfortunately it is not written in study books because this knowledge not a compilation of MySQL manual but result of long experience and struggling with slowness.

I do not talk about indexes on fields where WHERE is used. And other things like that. That is not Pro tip:) It is kind of regular. So I'll try to go beyond simple suggestions.

• May be some of my explanations are not completely right when I try to explain the proces behind but in general we are talking about results.

To understand main idea behind lets take two tables.

users

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_block` (`block`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` (`name`, `email`, `password`, `block`) VALUES ('jon', 'test@test.com', '123', 0);
INSERT INTO `users` (`name`, `email`, `password`, `block`) VALUES ('Eddy', 'eddy@test.com', '321', 1);

and user's attributes

CREATE TABLE `user_profiles` (
  `user_id` int(11) NOT NULL,
  `profile_key` varchar(100) NOT NULL,
  `profile_value` varchar(255) NOT NULL,
  UNIQUE KEY `idx_user_id_profile_key` (`user_id`,`profile_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user_profiles` (`user_id`, `profile_key`, `profile_value`) 
VALUES (1, 'moderator', '1'), (2, 'moderator', '0'), 
(1, 'email_sent', '1'), (2, 'email_sent', '1'), 
(1, 'registered', '2012-01-01 00:00:00'), (2, 'registered', '2012-01-02 00:00:00');

We all know that worse case is when we gave using temporary and using file sort in one line in explain. All queries seems to be Ok until you start using ORDER BY or/and GROUP BY

This is what we gonna solve. To restructure query so it only have Using were.

Now lets look at most seriose error in construction of queries.

1. Avoid of use LEFT JOIN if its aliases used in WHERE. This is most important lesson I had to learn.

SELECT * FROM
    users AS u
        LEFT JOIN user_profiles AS p ON p.user_id = u.id 
WHERE 1
    AND u.block = 1 
    AND (p.profile_key = 'moderator' AND p.profile_value = 1)
ORDER BY u.name

Better to use subqueries in this case.

SELECT * FROM
    users AS u
WHERE 1
    AND u.block = 1 
    AND u.id IN (SELECT user_id 
        FROM user_profiles AS p 
        WHERE p.user_id = u.id 
        AND p.profile_key = 'moderator' 
        AND p.profile_value = 1)
ORDER BY u.name

Let me explain more on this. This is like we would create temporary table, insert there all moderators, and then use it as an index table. And this main princip of query optimisation. Look toward subqueries.

This technique will also let you to avoid to ever use GROUP BY. Because you use id IN() even if it is id IN(1,2,1,3,4,2,2,2) only 4 records will be returned. And GROUP BY is a great headache.

pro-pro tip :) I have discovered that sometimes it is quicker to run subquery separately and form string like 1,2,8,47,12 and insert it in IN() as string but not as subquery.

2. Avoid sorting by field in JOIN. Like for example attempt to sort by profile value registered.

SELECT * FROM
    users AS u
        LEFT JOIN user_profiles AS p ON p.user_id = u.id AND p.profile_key = 'registered' 
WHERE 1
    AND u.block = 1 
ORDER BY p.profile_value ASC

better to use subqueries.

SELECT u.*, 
    (SELECT profile_value 
        FROM user_profiles 
        WHERE user_id = u.id 
        AND profile_key = 'registered') as reg 
    FROM
    users AS u
WHERE 1
    AND u.block = 1 
ORDER BY reg DESC

   SELECT * FROM users AS u
LEFT JOIN user_profiles AS p 
       ON p.user_id = u.id 
    WHERE 1
      AND u.block = 1 
      AND (p.profile_key = 'moderator' AND p.profile_value = 1)
 ORDER BY u.name