How to ensure the query use the index?

Question

My query is slow.

SELECT
    posts.*
FROM
    posts
    INNER JOIN categories ON posts.category_id = categories.id
        AND categories.main = 1
        AND(categories.private_category = 0
            OR categories.private_category IS NULL)
WHERE
    posts.id NOT IN('')
    AND posts.deleted = 0
    AND posts.hidden = 0
    AND posts.total_points >= - 5
ORDER BY
    posts.id DESC
LIMIT 10;

So after I explain it:

| id | select_type | table       | partitions | type | possible_keys                                                                                                                                                    | key                         | key_len | ref                           | rows  | filtered | Extra                                        |
|----|-------------|-------------|------------|------|------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------|---------|-------------------------------|-------|----------|----------------------------------------------|
|  1 | SIMPLE      | categories  |            | ALL  | PRIMARY,index_categories_on_private_category                                                                                                                     |                             |         |                               |    12 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | posts       |            | ref  | PRIMARY,index_posts_on_category_id,index_posts_on_deleted_and_hidden_and_user_id_and_created_at,index_posts_deleted,index_posts_hidden,index_posts_total_points  | index_posts_on_category_id  | 5       | mydb.categories.id            | 37516 |    12.50 | Using index condition; Using where           |

I added an index on categories.main:

| id | select_type | table       | type   | possible_keys                                                                                                                                                    | key     | key_len | ref                               | rows  | Extra       |
|----|-------------|-------------|--------|------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------|---------|-----------------------------------|-------|-------------|
|  1 | SIMPLE      | posts       | range  | PRIMARY,index_posts_on_category_id,index_posts_on_deleted_and_hidden_and_user_id_and_created_at,index_posts_deleted,index_posts_hidden,index_posts_total_points  | PRIMARY | 4       |                                   | 37516 | Using where |
|  1 | SIMPLE      | categories  | eq_ref | PRIMARY,index_categories_on_private_category,index_categories_on_main                                                                                            | PRIMARY | 4       | mydb.posts.category_id            |    12 | Using where |

it shows that it does not use the indexing(?). the query is still slow and I want to optimize it. What is the wrong with the query?

Edit

This how the posts table created:

CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(1000) NOT NULL,
  `content` text,
  `total_points` int(11) DEFAULT '0',
  `deleted` tinyint(1) DEFAULT '0',
  `hidden` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_posts_on_category_id` (`category_id`),
  KEY `index_posts_created_at` (`created_at`),
  KEY `index_posts_on_deleted_and_hidden_and_user_id_and_created_at` (`deleted`,`hidden`,`user_id`,`created_at`),
  KEY `index_posts_deleted` (`deleted`),
  KEY `index_posts_hidden` (`hidden`),
  KEY `index_posts_total_points` (`total_points`),
  KEY `index_posts_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=304063 DEFAULT CHARSET=utf8

This how the categories table created:

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `main` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `hidden` tinyint(1) DEFAULT '0',
  `private_category` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_categories_on_private_category` (`private_category`),
  KEY `index_categories_on_main` (`main`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

The main problem is that the query planner thinks it's best the acces `categories` first before the `posts` table making fewer records but it need to store a temporary table `Using temporary;` which can be memory or disk based... also ` Using filesort ` is needed filesort is a weird name but this suggestes a quicksort algorithm is run on the data — Raymond Nijland, May 30 '19 at 12:54
But answers asking for performance should include the `SHOW CREATE TABLE table` output as well for every table involved in the question.. — Raymond Nijland, May 30 '19 at 12:55
> the query planner thinks...before the posts. From what part did you conclude that? Thanks. — MIA, May 30 '19 at 12:59
The explain plain order.. if you would run `SELECT STRAIGHT_JOIN posts.*` you would notice the order will change and that most likely `Using temporary; Using filesort` is also gone asumming correct indexes.. **Warning** `STRAIGHT_JOIN` is not a solution as one day it can work fine and the other not also it does not defect a optimizer state `impossible WHERE` when the index does not match or can't match the `WHERE` filters so it will run/execute annyway.. — Raymond Nijland, May 30 '19 at 13:04
*"it shows that it does not use the indexing(?). the query is still slow and I want to optimize it. What is the wrong with the query?"* Making a index does not mean MySQL has to use it.. Optimizers are costs based, The second explain shows that using the PRIMARY KEY from both table was the most cheapest method to get the data. — Raymond Nijland, May 30 '19 at 13:11
Also i believe upgrading to MySQL 8.0 would also help as that optimizer tends to try avoiding `Using temporary; Using filesort` as much as possible at the costs of possibly scanning more records in the table/index files..`Using temporary; Using filesort` combination can be a real killer for MySQL performance when large tables/resultsets are involved. — Raymond Nijland, May 30 '19 at 13:15
I have just added the `SHOW CREATE TABLE table` output. If you can help with optimizing this that would be great. Anyway thank you so much for your help. — MIA, May 30 '19 at 13:19
i have a feeling you changed the create statements? As the `posts` table statement is not valid `KEY `index_posts_user_id` (`user_id`),` # <- extra comma and the `total_points ` column is missing? — Raymond Nijland, May 30 '19 at 13:19
Also some example and expected result would also help see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) .. if a query rewrite is needed we can atleast verify the correctness — Raymond Nijland, May 30 '19 at 13:22
_i have a feeling you changed the create statements?_ Yes. I just fixed it. — MIA, May 30 '19 at 13:32
You main problem is caused by bad indexes a.k.a [index shotgunning](https://www.oreilly.com/library/view/sql-antipatterns/9781680500073/f_0081.html), i advice you to look at [this](https://www.db-fiddle.com/f/dbxnMqsUb7ytp7Zu61csQa/9) — Raymond Nijland, May 30 '19 at 14:41

score 1 · Answer 1 · answered May 30 '19 at 12:21

1

The query is slow because you use OR condition:

categories.private_category = 0 OR categories.private_category IS NULL

MySQL have to scan all records in categories table

answered May 30 '19 at 12:21

Michael Krutikov

484
1
4
10

Thanks! Can you give a hint about how to go around this. – MIA May 30 '19 at 12:30
Look that question, it might be helpfull: https://stackoverflow.com/questions/2829544/mysql-how-to-index-an-or-clause – Michael Krutikov May 30 '19 at 12:55
2

@MIA *"The query is slow because you use OR condition:"* *"MySQL have to scan all records in categories table"* No MySQL can optimize OR on one column just fine see [demo](https://www.db-fiddle.com/f/msTJNbuf3Vh2Cb4Wbo5F7X/2) .. The OR condition on multiple (different) columns is a problem like `column1 = 0 OR column2 iS NULL` which you can best rewrite to a `UNION` approach which some optimizer do automatic like Oracle database. – Raymond Nijland May 30 '19 at 13:01

How to ensure the query use the index?

1 Answers1