CODEWITHSUNDEEP
mysql
MIA
MIA

Reputation: 431

How to ensure the query use the index?

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

Upvotes: 1

Views: 56

Answers (1)

Michael Krutikov
Michael Krutikov

Reputation: 492

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

Upvotes: 1

Related Questions