Query ignores index resulting full table scan

Question

I have two tables from django in mysql 5.7, auth_user and account_account:

CREATE TABLE `auth_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `email` varchar(75) NOT NULL,
  `password` varchar(128) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `date_joined` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `is_staff` (`is_staff`)
) ENGINE=InnoDB AUTO_INCREMENT=4438 DEFAULT CHARSET=utf8;

CREATE TABLE `account_account` (
  `user_ptr_id` int(11) NOT NULL,
  `org_form` smallint(5) unsigned DEFAULT NULL,
  `org_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `contact_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `balance` decimal(15,2) NOT NULL,
  `total_debt` decimal(15,2) NOT NULL,
  `contact_email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `mob_phone` varchar(255) NOT NULL,
  `other_phone` varchar(255) NOT NULL,
  `delivery_methods` smallint(5) unsigned NOT NULL,
  `delivery_address` longtext NOT NULL,
  `payment_method` smallint(5) unsigned NOT NULL,
  `person_type` smallint(5) unsigned NOT NULL,
  `client_type` smallint(5) unsigned NOT NULL,
  `city` smallint(5) unsigned NOT NULL DEFAULT '1',
  `patronymic` varchar(255) NOT NULL,
  `can_delay` tinyint(1) NOT NULL,
  `status` smallint(5) unsigned NOT NULL,
  `block_reason` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `manager_id` int(11) DEFAULT NULL,
  `wholesale_level_id` int(11) DEFAULT NULL,
  `markup_type` smallint(5) unsigned NOT NULL,
  `org_markup_level_id` int(11) DEFAULT NULL,
  `use_vat` tinyint(1) NOT NULL,
  `last_order_date` datetime NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  `is_partner` tinyint(1) NOT NULL,
  `partner_markup` decimal(15,2) DEFAULT NULL,
  `call_markup` decimal(15,2) DEFAULT NULL,
  `delivery_price` decimal(15,2) DEFAULT NULL,
  `city_id` int(11) NOT NULL,
  `unloading_at` time NOT NULL,
  `site` varchar(200),
  `ip_address_id` varchar(20) DEFAULT NULL,
  `ip_restriction` tinyint(1) NOT NULL,
  `visualisation_id` int(11),
  `_express_delivery_price` int(10) unsigned DEFAULT NULL,
  `is_express_delivery` tinyint(1) NOT NULL,
  `payment_type` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`user_ptr_id`),
  KEY `account_account_501a2222` (`manager_id`),
  KEY `account_account_7c811b3c` (`wholesale_level_id`),
  KEY `group_id_refs_id_35dd1116` (`group_id`),
  KEY `city_id_refs_id_96652cf` (`city_id`),
  KEY `account_account_ip_address_id_e42ae8c3_uniq` (`ip_address_id`),
  KEY `account_ac_visualisation_id_cd5729f6_fk_account_visualisation_id` (`visualisation_id`),
  CONSTRAINT `account_ac_visualisation_id_cd5729f6_fk_account_visualisation_id` FOREIGN KEY (`visualisation_id`) REFERENCES `account_visualisation` (`id`),
  CONSTRAINT `account_accou_ip_address_id_e42ae8c3_fk_account_ipadress_address` FOREIGN KEY (`ip_address_id`) REFERENCES `account_ipadress` (`address`),
  CONSTRAINT `account_account_city_id_2ab1a62f_fk_classifiers_city_id` FOREIGN KEY (`city_id`) REFERENCES `classifiers_city` (`id`),
  CONSTRAINT `group_id_refs_id_35dd1116` FOREIGN KEY (`group_id`) REFERENCES `account_clientgroup` (`id`),
  CONSTRAINT `user_ptr_id_refs_id_17b5ed9e` FOREIGN KEY (`user_ptr_id`) REFERENCES `auth_user` (`id`),
  CONSTRAINT `wholesale_level_id_refs_id_369bcb86` FOREIGN KEY (`wholesale_level_id`) REFERENCES `classifiers_wholesalelevel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm trying to do query:

explain     SELECT  `auth_user`.`id`, `account_account`.`contact_name`, `account_account`.`contact_email`,
            `account_account`.`phone`
        FROM  `account_account`
        INNER JOIN  `auth_user`  ON (`account_account`.`user_ptr_id` = `auth_user`.`id`)
        WHERE  (`auth_user`.`is_staff` = 0)
        LIMIT  16;

+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table           | partitions | type   | possible_keys    | key     | key_len | ref                               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+
|  1 | SIMPLE      | account_account | NULL       | ALL    | PRIMARY          | NULL    | NULL    | NULL                              | 3741 |   100.00 | NULL        |
|  1 | SIMPLE      | auth_user       | NULL       | eq_ref | PRIMARY,is_staff | PRIMARY | 4       | raros.account_account.user_ptr_id |    1 |   100.00 | Using where |
+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+

It shows that account_account does full scan even if we do not do any filtering on account, only join. Can you explain to me two things:

1. why mysql does not take is_staff index for initial filtering? we have two indexes on auth_user: primary and is_staff
2. why mysql does full scan on account_account, even if we do not do any filtering for account_account table?

Thanks in advance!

Answer 1

It may be that it is quite optimal the way you have it.

First note that the ALL and 3741 Rows in EXPLAIN are not necessarily meaningful. This is because EXPLAIN rarely notices LIMIT when listing such data.

Let's suppose it does do ALL:

1. Scan the entire table of about 3741 rows. Do it in any order, since there is nothing to constrain the scan (such as an ORDER BY).
2. For each of those rows, reach into the other table.
3. Reaching into auth_user via the PRIMARY KEY is quite efficient.
4. Check is_staff. If it is usually 0, then all is well. If it is rarely 0, then there is a lot of wasted effort.
5. Stop after 16 rows. So, depending on is_staff, this required looking at between 16 and 3741 rows.

Add an ORDER BY; then let's re-Explain it.