All geek questions in one place

Mysql left join, where with or, scan the entire table (without index)

I tried this in several different ways and get bad results.

The main problem is that Member Search crawls ALL elements, ignoring indexes.

The main reason (from what I can say) is the fragment

(Member.priv_profile = 3 OR MyFriend.status_id IN (1,2))

Either side of just this OR fragment works fine, gets the index, looks at a few lines, and thus works well.

I really do not want to split this request into 2 and make UNION, but we may have to do this if someone does not come up with a good way to make this choice "work" with an important OR.

 mysql> ALTER TABLE `members` ADD INDEX A (is_active, last_name, first_name); Query OK, 140019 rows affected (6.82 sec) Records: 140019 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE `members` ADD INDEX B (is_active, last_name, first_name, priv_profile); Query OK, 140019 rows affected (7.70 sec) Records: 140019 Duplicates: 0 Warnings: 0 mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365') WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2)); +----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+ | 1 | SIMPLE | Member | ALL | active_delete,scope,member_search_alerts,A,B | NULL | NULL | NULL | 140019 | Using where | | 1 | SIMPLE | MyFriend | ref | member_1_id | member_1_id | 4 | const | 155 | Using where; Using index | +----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+ 2 rows in set (0.00 sec) // without the "public profile" part mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365') WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`MyFriend`.`status_id` IN (1,2)); +----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+ | 1 | SIMPLE | MyFriend | range | member_1_id | member_1_id | 5 | NULL | 251 | Using where; Using index | | 1 | SIMPLE | Member | eq_ref | PRIMARY,active_delete,scope,member_search_alerts,A,B | PRIMARY | 4 | ao_prod.MyFriend.member_2_id | 1 | Using where | +----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+ 2 rows in set (0.00 sec) // without the "my connection" part mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '42983') WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`Member`.`priv_profile` = 3); +----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+ | 1 | SIMPLE | Member | ref | active_delete,scope,member_search_alerts,A,B | scope | 2 | const,const | 2007 | Using where | | 1 | SIMPLE | MyFriend | ref | member_1_id | member_1_id | 4 | const | 252 | Using index | +----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+ 2 rows in set (0.01 sec) // as a subquery vs. join (no workie) mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND ( `Member`.`id` IN ( SELECT member_2_id FROM member_friends WHERE member_1_id = 150365 AND status_id IN (1,2) )); +----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+ | 1 | PRIMARY | Member | ALL | active_delete,scope,member_search_alerts,A,B | NULL | NULL | NULL | 140019 | Using where | | 2 | DEPENDENT SUBQUERY | member_friends | range | member_1_id | member_1_id | 5 | NULL | 155 | Using where; Using index | +----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+ 2 rows in set (0.01 sec) // sketch of the possible, ugly UNION mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '42983') WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`MyFriend`.`status_id` IN (1,2)) -> UNION -> SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`Member`.`priv_profile` = 3) -> GROUP BY Member.id -> ; +----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | MyFriend | range | member_1_id | member_1_id | 5 | NULL | 251 | Using where; Using index | | 1 | PRIMARY | Member | eq_ref | PRIMARY,active_delete,scope,member_search_alerts,A,B | PRIMARY | 4 | ao_prod.MyFriend.member_2_id | 1 | Using where | | 2 | UNION | Member | ref | active_delete,scope,member_search_alerts,A,B | scope | 2 | const,const | 2007 | Using where; Using temporary; Using filesort | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+ 4 rows in set (0.02 sec) // using index hinting to no avail mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` USE INDEX (A) LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365') WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2)); +----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+ | 1 | SIMPLE | Member | ALL | A | NULL | NULL | NULL | 140245 | Using where | | 1 | SIMPLE | MyFriend | ref | member_1_id | member_1_id | 4 | const | 181 | Using where; Using index | +----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+ 2 rows in set (0.01 sec)

Here are the creation instructions for the involved tables (full, ugly tables and all the other indexes shown)

 CREATE TABLE IF NOT EXISTS `member_friends` ( `id` varchar(36) NOT NULL, `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, `member_1_id` int(11) NOT NULL DEFAULT '0', `member_2_id` int(11) NOT NULL DEFAULT '0', `status_id` tinyint(3) NOT NULL DEFAULT '0', `requested_by` tinyint(3) NOT NULL DEFAULT '0', `requested` datetime DEFAULT NULL, `accepted` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `member_1_id` (`member_1_id`,`status_id`,`member_2_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `members_fields` ADD PRIMARY KEY (`id`), ADD KEY `key` (`key`), ADD KEY `member_key` (`member_id`,`key`); CREATE TABLE IF NOT EXISTS `members` ( `id` int(11) NOT NULL, `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, `profile_updated` datetime NOT NULL, `last_login` datetime DEFAULT NULL, `is_active` tinyint(1) NOT NULL, `email` varchar(256) NOT NULL DEFAULT '', `password` varchar(40) NOT NULL, `first_name` varchar(128) NOT NULL DEFAULT '', `middle_name` varchar(128) NOT NULL, `last_name` varchar(128) NOT NULL DEFAULT '', `suffix` varchar(32) NOT NULL, `company` varchar(128) NOT NULL, `address` varchar(128) NOT NULL, `address_2` varchar(128) NOT NULL, `city` varchar(128) NOT NULL, `state` varchar(5) NOT NULL, `zip` varchar(16) NOT NULL, `location_name` varchar(128) NOT NULL, `image_url` varchar(256) NOT NULL, `slug` varchar(64) NOT NULL, `headline` varchar(256) NOT NULL, `experience_level` varchar(64) NOT NULL, `apply_job_states` varchar(256) NOT NULL COMMENT 'CSV list', `apply_job_us` tinyint(1) NOT NULL DEFAULT '0', `apply_job_ca` tinyint(1) NOT NULL DEFAULT '0', `apply_job_traveling` tinyint(1) NOT NULL DEFAULT '0', `apply_job_international` tinyint(1) NOT NULL DEFAULT '0', `apply_job_fulltime` tinyint(1) NOT NULL DEFAULT '0', `apply_job_parttime` tinyint(1) NOT NULL DEFAULT '0', `apply_job_perdiem` tinyint(1) NOT NULL DEFAULT '0', `contact_for_professional_opportunities` tinyint(1) NOT NULL DEFAULT '0', `contact_for_job_inquiries` tinyint(1) NOT NULL DEFAULT '0', `contact_for_new_ventures` tinyint(1) NOT NULL DEFAULT '0', `contact_for_expertise_requests` tinyint(1) NOT NULL DEFAULT '0', `country` varchar(2) NOT NULL, `timezone` varchar(32) NOT NULL, `phone` varchar(16) NOT NULL, `fax` varchar(16) NOT NULL, `birthday` varchar(5) NOT NULL COMMENT 'MM/DD (required)', `birth_year` varchar(4) DEFAULT NULL COMMENT 'YYYY (optional)', `corp_id` int(11) NOT NULL DEFAULT '0', `is_deleted` tinyint(1) NOT NULL, `url` varchar(256) DEFAULT NULL, `emails` varchar(512) NOT NULL COMMENT 'JSON list of alternate emails', `phones` varchar(512) NOT NULL COMMENT 'JSON list of alternate phones', `lat` float NOT NULL, `lon` float NOT NULL, `facebook_id` varchar(32) NOT NULL, `connect_id` int(11) NOT NULL, `is_student` tinyint(1) NOT NULL DEFAULT '0', `is_career_center_recruiter` tinyint(1) NOT NULL DEFAULT '0', `is_continuing_education_portal_manager` tinyint(1) NOT NULL DEFAULT '0', `is_manually_approved` tinyint(1) NOT NULL DEFAULT '0', `is_employer` tinyint(1) NOT NULL DEFAULT '0', `is_jobseeker` tinyint(1) NOT NULL DEFAULT '0', `is_jobseeker_badge` tinyint(1) NOT NULL DEFAULT '0', `is_contributor` tinyint(1) NOT NULL DEFAULT '0', `priv_profile` tinyint(3) NOT NULL DEFAULT '1', `priv_email` tinyint(3) NOT NULL DEFAULT '0', `priv_phone` tinyint(3) NOT NULL DEFAULT '0', `has_certification` tinyint(1) DEFAULT NULL, `has_state_license` tinyint(1) DEFAULT NULL, `job_title` varchar(64) NOT NULL, `occupation_id` int(11) NOT NULL, `occupation_other` varchar(64) NOT NULL, `work_setting_id` int(11) NOT NULL, `work_setting_other` varchar(64) NOT NULL, `memberships_honors_awards` text NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1093688 ; ALTER TABLE `members` ADD PRIMARY KEY (`id`), ADD KEY `is_cc` (`is_career_center_recruiter`,`corp_id`), ADD KEY `is_ce` (`is_continuing_education_portal_manager`,`corp_id`), ADD KEY `corp_id` (`corp_id`), ADD KEY `active_delete` (`is_active`,`is_deleted`), ADD KEY `delete` (`is_deleted`), ADD KEY `email_pass` (`email`,`password`), ADD KEY `apply_job_states` (`apply_job_states`,`apply_job_us`,`apply_job_ca`), ADD KEY `experience_level` (`experience_level`), ADD KEY `latlon` (`lat`,`lon`), ADD KEY `location` (`state`,`zip`), ADD KEY `slug` (`slug`,`is_active`,`priv_profile`), ADD KEY `scope` (`is_active`,`priv_profile`,`state`), ADD KEY `member_search_alerts` (`is_active`,`is_jobseeker`,`profile_updated`,`priv_profile`,`apply_job_us`,`apply_job_ca`);

UPDATE: as requested, here are the optimizer settings

 mysql> SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on 1 row in set (0.00 sec)

NOTE: this has been tested on

  • Server Version: 5.6.20-68.0-56-log - Percona XtraDB Cluster (GPL), Release 25.7
  • Server Version: 5.5.29-0ubuntu0.12.04.1
  • Server Version: 5.1.72 - Source Distribution
+5
source share
2 answers

In this case, one of the tables was MyISAM and the other was InnoDB

When I switched both to InnoDB , it magically changed from ALL to ref and from scanning all rows to a subset.

 mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365') WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2)); +----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+ | 1 | SIMPLE | Member | ALL | A | NULL | NULL | NULL | 140245 | Using where | | 1 | SIMPLE | MyFriend | ref | member_1_id | member_1_id | 4 | const | 181 | Using where; Using index | +----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE `members` ENGINE = InnoDB; Query OK, 140245 rows affected (1 min 8.10 sec) Records: 140245 Duplicates: 0 Warnings: 0 mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365') WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2)); +----+-------------+----------+------+---------------+-------------+---------+-------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------------+---------+-------+-------+--------------------------+ | 1 | SIMPLE | Member | ref | A | A | 1 | const | 53916 | Using where | | 1 | SIMPLE | MyFriend | ref | member_1_id | member_1_id | 4 | const | 181 | Using where; Using index | +----+-------------+----------+------+---------------+-------------+---------+-------+-------+--------------------------+
+2
source

Creating these two tables along with these two indexes and executing the first query actually uses index A for the memebers table:

 +----+-------------+----------+------+---------------+-------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | Member | ref | A,B | A | 1 | const | 3199 | Using index condition | | 1 | SIMPLE | MyFriend | ref | member_1_id | member_1_id | 4 | const | 2 | Using where; Using index | +----+-------------+----------+------+---------------+-------------+---------+-------+------+--------------------------+

Tested: 5.6.19-0ubuntu0.14.04.1

Also on SQLFiddle

0
source

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

More articles:


All Articles