2

I have two indexes on the same column:

  1. store_deleted_idx (store_id, deleted)
  2. store_idx (store_id)

DDL:

CREATE TABLE `stores_shoppers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_id` int(11) NOT NULL,
  `store_shopper_id` varchar(128) NOT NULL,
  `country_code` varchar(30) DEFAULT NULL,
  `zip_code` varchar(50) DEFAULT NULL,
  `email` varchar(128) NOT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `modified_on` datetime NOT NULL,
  `is_marketing_allowed` tinyint(1) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `is_guest` tinyint(1) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `total_spent` decimal(12,2) DEFAULT NULL,
  `orders_count` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `store_idx` (`store_id`),
  KEY `store_deleted_idx` (`store_id`,`deleted`),
  CONSTRAINT `_stores_shoppers_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`),
  CONSTRAINT `_stores_shoppers_ibfk_4` FOREIGN KEY (`group_id`) REFERENCES `stores_shopper_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=56909900 DEFAULT CHARSET=utf8;

My query:

SELECT s.*, t.*, g.*
FROM shoppers s
LEFT JOIN shopper_tag t ON s.id = t.shopper_id
LEFT JOIN shopper_group g ON s.group_id = g.id
WHERE s.store_id = '4494' AND (s.deleted = 0 OR s.deleted IS NULL)
ORDER BY s.created_on DESC
LIMIT 10 OFFSET 0;

If I run the explain, it shows the query is using only store_idx index, but if I remove store_idx index and run the explain, it shows store_deleted_idx is used.

Why isn't store_deleted_idx used in the first case?

I can't remove the single-column index.

  • Please show us the DDL for the table in question. – Dave Aug 6 '18 at 10:52
  • 1
    You want to say that using complex index gives higher performance than that with single index? If so use index hints. Why don't use with store_deleted_idx? To use index its cardinality is to be high. What's cardinality of tinyint(1) null? 11 max (including nulls)... and I think 3 (including nulls) in your case. I can't remove simple index. ?? Why? unsufficient rights? – Akina Aug 6 '18 at 11:04
  • One more - try to convert your query to WHERE (s.store_id = '4494' AND s.deleted = 0) OR (s.store_id = '4494' AND s.deleted IS NULL). – Akina Aug 6 '18 at 11:05
  • No, I want understand why mysql not used with complex index – kostyaR Aug 6 '18 at 11:13
  • @Akina One more - try to convert your query to WHERE (s.store_id = '4494' AND s.deleted = 0) OR (s.store_id = '4494' AND s.deleted IS NULL) - same result – kostyaR Aug 6 '18 at 11:18
1

Get rid of OR

(s.deleted = 0 OR s.deleted IS NULL)

Make up your mind. Either use 0 (and make it NOT NULL) or use NULL. It is probably better to have

TINYINT UNSIGNED NOT NULL DEFAULT '0'

Until you get rid of the OR, an index including deleted is not useful.

Composite index

After that, INDEX(deleted, store_id), in either order, becomes optimal.

As a rule, INDEX(a) is unnecessary if you also have INDEX(a,b).

Even better is

INDEX(deleted, store_id,   -- in either order
      created_at)          -- last

This will avoid the sorting that is currently necessary. (See EXPLAIN SELECT ...)

I can't remove the single-column index.

Do you mean that your fingers don't work? Your boss will slap your hand if you try? You don't have permissions? It comes back when you do remove it? The FOREIGN KEY gets in the way? What?

Cardinality

Fake news.

Within a composite index, cardinality is irrelevant.

When comparing two indexes, cardinality is important, but there are usually better ways to optimize, such as using a composite index. So, again, fake news.

Back to the question

Your experiments showed that either INDEX(store_id) or INDEX(store_id, deleted) would be used. Given a choice between the two of them, the Optimizer will pick the smaller one (on the presumption that the amount of effort is slightly less). Without a choice (ie, when you had deleted the smaller one), the Optimizer happily picked whichever one is left.

So, to avoid wasting disk space, DROP the shorter (single-column) INDEX. Meanwhile this query will continue to work efficiently.

"Why isn't store_deleted_idx used in the first case?" -- Because of the OR.

More on indexing

http://mysql.rjweb.org/doc.php/index_cookbook_mysql discusses how getting all the way through the WHERE (after getting rid of OR) lets the index get to the ORDER BY, and if you get through the ORDER BY (by tacking on created_by), you can get to LIMIT. Then it will read only 10 rows, not the whole table!

  • @kostyaR - I talked around it, but failed to address your question. I now added "Back to the question" section. – Rick James Dec 17 '18 at 17:44
0

I found an explanation in this Stack Overflow answer:

Max cardinality: All values are unique

Min cardinality: All values are the same

Some columns are called high-cardinality columns because they have constraints in place (like unique) prohibiting you from putting the same value in every row.

Cardinality is a property which affects the ability to cluster, sort and search data. It is therefore an important measurement for the query planners in DBs, it is a heuristic which they can use to choose the best plans.

I ran SHOW INDEX FROM table_name. My index 'cardinality store_id' is 2380 and index 'store_deleted_idx' - store_id 2380 and deleted 12

Therefore, MySQL selects the first index!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.