All geek questions in one place

How to optimize MySQL query with big data in the left join?

The answer below returns the User set and for each row the number of relationships from the user who is looking for (id = 4)

SELECT `users`.`firstname` AS firstname,
       `users`.`lastname` AS lastname,
       COUNT(`trusted_users`.`id`) AS number_of_friend_in_common,
       CASE ... AS friend,
       CASE ... AS facebook_invitable,
       CASE ... AS address_book_invitable,
       CASE ... AS virtual_user,
FROM `users`

LEFT OUTER JOIN `trusted_users` 
  ON `trusted_users`.`user_id` = 4 AND `trusted_users`.`trust_user_id` = `users`.`id`

LEFT OUTER JOIN `facebook_friends` 
  ON (`facebook_friends`.`user_id` = 4 AND `facebook_friends`.`friend_user_id` = `users`.`id`
  OR `facebook_friends`.`user_id` = `users`.`id` AND `facebook_friends`.`friend_user_id` = 4)

LEFT OUTER JOIN `address_book_contacts` 
  ON `address_book_contacts`.`owner_id` = 4 AND `address_book_contacts`.`email_digest` = `users`.`email_digest`

LEFT OUTER JOIN `friends` 
  ON (`friends`.`me_id` = `users`.`id` AND `friends`.`him_id` = 4
  OR `friends`.`me_id` = 4 AND `friends`.`him_id` = `users`.`id`)

WHERE `users`.`id` NOT IN
    (SELECT CASE
                WHEN `friends`.`me_id` = 4 THEN `friends`.`him_id`
                ELSE `friends`.`me_id`
            END
     FROM `friends`
     WHERE (`friends`.`status` = 0
            AND `friends`.`him_id` = 4
            AND `friends`.`him_status` = 7
            OR `friends`.`status` = 0
            AND `friends`.`me_id` = 4
            AND `friends`.`me_status` = 7))
  AND (`users`.`firstname` LIKE '%a%' OR `users`.`lastname` LIKE '%a%')
GROUP BY `users`.`id`
ORDER BY friend DESC,
         facebook_invitable DESC,
         address_book_invitable DESC,
         number_of_friend_in_common DESC,
         virtual_user DESC,
         firstname,
         lastname LIMIT 0, 20

The number of rows for each table:

trusted_users: 255k

facebook_friends: 1k

address_book_contacts: 1.5 m

friends: 70k

users: 32k

All union fields are indexed. The request accepts 1.1s, which is unacceptable for the amount of data that we have. What am I doing wrong? Should I break into multiple queries and break my own pages?

Edit 1: EXPLAIN Result

+----+-------------+-----------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+---------+----------------------------------+-------+----------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table                 | type        | possible_keys                                                                                                                                                               | key                                                       | key_len | ref                              | rows  | Extra                                                                                                                      |
+----+-------------+-----------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+---------+----------------------------------+-------+----------------------------------------------------------------------------------------------------------------------------+
|  1 | PRIMARY     | users                 | ALL         | PRIMARY,index_users_on_chat_id,index_users_login_facebook_id,index_users_on_login,index_users_on_parent_id,index_users_account_type,index_users_email_digest,index_users_id | NULL                                                      | NULL    | NULL                             | 31847 | Using where; Using temporary; Using filesort                                                                               |
|  1 | PRIMARY     | trusted_users         | ref         | index_trusted_users_user,index_trusted_users_trust_user                                                                                                                     | index_trusted_users_trust_user                            | 5       | messenger_dev.users.id           |     6 | Using where                                                                                                                |
|  1 | PRIMARY     | facebook_friends      | index_merge | index_facebook_friends_user,index_facebook_friends_friend                                                                                                                   | index_facebook_friends_user,index_facebook_friends_friend | 5,5     | NULL                             |     2 | Using union(index_facebook_friends_user,index_facebook_friends_friend); Using where; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY     | address_book_contacts | ref         | index_address_book_contacts_owner_id,index_address_book_contacts_email                                                                                                      | index_address_book_contacts_email                         | 767     | messenger_dev.users.email_digest |     1 | Using where                                                                                                                |
|  1 | PRIMARY     | friends               | index_merge | index_friends_me_him,index_friends_me,index_friends_him                                                                                                                     | index_friends_him,index_friends_me                        | 5,5     | NULL                             |    18 | Using union(index_friends_him,index_friends_me); Using where; Using join buffer (Block Nested Loop)                        |
|  2 | SUBQUERY    | friends               | index_merge | index_friends_me_him,index_friends_me,index_friends_him                                                                                                                     | index_friends_him,index_friends_me                        | 5,5     | NULL                             |    18 | Using union(index_friends_him,index_friends_me); Using where                                                               |
+----+-------------+-----------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+---------+----------------------------------+-------+----------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0,00 sec)

Edit 2: Table structure

CREATE TABLE `address_book_contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_digest` varchar(191) DEFAULT NULL,
  `code` varchar(191) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_address_book_contacts_owner_id` (`owner_id`),
  KEY `index_address_book_contacts_email` (`email_digest`)
) ENGINE=InnoDB AUTO_INCREMENT=1598109 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `trusted_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `trust_user_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_trusted_users_on_friend_id` (`friend_id`),
  KEY `index_trusted_users_user` (`user_id`),
  KEY `index_trusted_users_trust_user` (`trust_user_id`),
  CONSTRAINT `fk_rails_007c31c802` FOREIGN KEY (`trust_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_rails_ca24cb4e23` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=275576 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `facebook_friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `friend_user_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `code` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_facebook_friends_user` (`user_id`),
  KEY `index_facebook_friends_friend` (`friend_user_id`),
  KEY `index_facebook_friends_code` (`code`(5)),
  CONSTRAINT `fk_rails_78285a074e` FOREIGN KEY (`friend_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_rails_aa3ac53a81` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1149 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `me_id` int(11) DEFAULT NULL,
  `him_id` int(11) DEFAULT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `me_status` int(11) DEFAULT '0',
  `him_status` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_friends_me_him` (`me_id`,`him_id`),
  KEY `index_friends_me` (`me_id`),
  KEY `index_friends_him` (`him_id`),
  KEY `index_friends_owner` (`owner_id`),
  CONSTRAINT `fk_rails_9fa3474d31` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_rails_d3ebb6657f` FOREIGN KEY (`him_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_rails_fccfd1b821` FOREIGN KEY (`me_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=95724 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_type` varchar(191) NOT NULL,
  `firstname` varchar(191) DEFAULT NULL,
  `lastname` varchar(191) DEFAULT NULL,
  `login` varchar(191) DEFAULT NULL,
  `avatar` varchar(191) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `locale` varchar(191) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `password_digest` varchar(191) DEFAULT NULL,
  `email_digest` varchar(191) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_users_on_login` (`login`),
  KEY `index_users_account_type` (`account_type`),
  KEY `index_users_email_digest` (`email_digest`),
  KEY `index_uses_firstname` (`firstname`),
  KEY `index_users_lastname` (`lastname`)
) ENGINE=InnoDB AUTO_INCREMENT=32516 DEFAULT CHARSET=utf8mb4;
+4
source share
1 answer

, MySQL users.

ANALYZE TABLE users;, EXPLAIN. rows 31847? , !

, OPTIMIZE TABLE users;, EXPLAIN. rows 31847? , !

, USE INDEX (PRIMARY) USE INDEX (users_id) FROM users .

, !

0

Source: https://habr.com/ru/post/1669050/

More articles:


All Articles