Question 196088 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – The SELECT would examine more than MAX_JOIN_SIZE rows without any JOINS

In MariaDB 11 database I have following table:

CREATE TABLE `tags` (
  `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id`  int(10) unsigned NOT NULL DEFAULT 1,
  `type`       varchar(20)      NOT NULL DEFAULT '',
  `name`       varchar(100)     NOT NULL DEFAULT '',
  `count`      int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp            NULL DEFAULT NULL,
  `updated_at` timestamp            NULL DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `tags_type_index`        (`type`),
  KEY `tags_domain_id_foreign` (`domain_id`),
  KEY `tags_name_index`        (`name`),
  KEY `tags_count_index`       (`count`) USING BTREE,
  CONSTRAINT `tags_domain_id_foreign` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

My query is following:

select
    *
from
    tags
where
    type = 'tag'
    and
    domain_id = 95
limit 10;

The total number of rows with this query is: 58,131 (without limit). Total number of records in the table is 1,466,914 rows.

I’m using MAX_JOIN_SIZE in my database to mitigate some issue with poorly optimized queries in a complex web app.

The issue I’m trying to understand why this causes error: "The SELECT would examine more than MAX_JOIN_SIZE rows" when I set SET MAX_JOIN_SIZE = 100000;?

It works however with: SET MAX_JOIN_SIZE = 200000;.

I’m curious cause firstly there is no joins in this query, secondly total rows number for type = 'tag' and domain_id = 95 is 58,131.

Tags:

2

Answers


  1. 0

    Even if the number of rows in the result set is below the value of max_join_size the server needs to access more than max_join_size rows to deliver the result.

    Let’s assume you have two identical tables t1 and t2 with one integer column without an index and 1000 identical unique values.

    SELECT a FROM t1 JOIN t2 WHERE t1.id=t2.id will return exact one row, but to get the result the server needs to compare 1000 x 1000 values.

    Your EXPLAIN output shows that the server needs to examine 166160 rows, which is over the size of max_join_size.

    See also sql_big_selects and max_join_size documentation.

    Login or Signup to reply.
  2. 0

    Query execution can only use one index to optimize the WHERE clause. According to your EXPLAIN result, it has chosen to use the index on the domain_id column. There are 166160 rows with domain_id = 95, it has to scan all of these to find the ones that also have type = 'tag'. This is more than max_join_size, so you get an error.

    Create a composite index with both columns, then the query can be satisfied entirely with the index.

    ALTER TABLE tags ADD INDEX `tags_type_domain_index` (type, domain_id);

    If you do this, you can drop the index on type, since a multi-column index serves as an index for all of its prefixes as well.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search