Форум пользователей MySQL

vasya · 02.03.2010 02:15:46

Оказывается для использования индексов при объединении таблиц должны не только совпадать типы полей, но и кодировки в случае объединения по строковому столбцу.

CREATE TABLE a (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

col1 VARCHAR(50) CHARACTER SET utf8 NOT NULL DEFAULT '',

UNIQUE INDEX (col1));

CREATE TABLE b (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

col1 VARCHAR(50) CHARACTER SET utf8 NOT NULL DEFAULT '',

UNIQUE INDEX (col1));

INSERT INTO a (col1) VALUES('one'),('two'),('three'),('four');

INSERT INTO b (col1) VALUES('one'),('ooo'),('two'),('xxx');

test > EXPLAIN SELECT * FROM a JOIN b USING(col1)\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: index

possible_keys: col1

          key: col1

      key_len: 152

          ref: NULL

         rows: 4

        Extra: Using index

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: eq_ref

possible_keys: col1

          key: col1

      key_len: 152

          ref: test.a.col1

         rows: 1

        Extra: Using index

2 rows in set (0.00 sec)

Как видим, индекс используется при объединении. Теперь

test > ALTER TABLE b MODIFY col1 VARCHAR(50) CHARACTER SET cp1251 NOT NULL DEFAULT '';

Query OK, 4 rows affected (0.81 sec)

Records: 4  Duplicates: 0  Warnings: 0

test > EXPLAIN SELECT * FROM a JOIN b USING(col1)\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: index

possible_keys: col1

          key: col1

      key_len: 152

          ref: NULL

         rows: 4

        Extra: Using index

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: index

possible_keys: NULL

          key: col1

      key_len: 52

          ref: NULL

         rows: 4

        Extra: Using where; Using index; Using join buffer

2 rows in set (0.03 sec)

Непонятно только почему в последнем случае он пишет possible_keys: NULL при том, что далее использует ключ. Обратная ситуация была бы понятна, а так.. Это глюк или нормальная ситуация, понятная опытному пользователю?

rgbeast · 02.03.2010 06:27:09

У меня он ведет себя иначе. Ключ не использует, видимо потому, что слишком мало записей, но possible_keys присутствует (mysql версии 5.0.45)

mysql> EXPLAIN SELECT * FROM a JOIN b USING(col1)\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: ALL

possible_keys: col1

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4

        Extra: 

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: ALL

possible_keys: col1

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: Using where

2 rows in set (0.00 sec)

mysql> ALTER TABLE b MODIFY col1 VARCHAR(50) CHARACTER SET cp1251 NOT NULL DEFAULT '';

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM a JOIN b USING(col1)\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4

        Extra: 

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: ALL

possible_keys: col1

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: Using where

2 rows in set (0.00 sec)

vasya · 02.03.2010 06:39:26

У меня он ключ использует, так как таблицы innodb. Вторичный ключ содержит указатель на primary, поэтому запрос может быть выполнен без обращения к данным. Если на myisam тестить, то действительно нужно больше данных, я потому и юзал innodb. Лень двигатель прогресса

Интересно, а почему он у тебя во втором случае поменял порядок объединения и почему-то тоже possible_keys: NULL, хотя по логике ключ использовать-то можно, другой вопрос, что не целесообразно.

У меня стоит 5.1.21-beta-community-log. Я почему и подумал, что может глюк данной бета-версии.

rgbeast · 02.03.2010 06:45:30

Добавил данных, он стал использовать ключ. ref: func, то есть он просто считает, что нужно выполнить конвертацию, а затем использовать индекса. Так что разная кодировка ему не мешает.

mysql> EXPLAIN SELECT * FROM a JOIN b USING(col1)\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 8

        Extra: 

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: eq_ref

possible_keys: col1

          key: col1

      key_len: 152

          ref: func

         rows: 1

        Extra: Using where

2 rows in set (0.00 sec)

vasya · 02.03.2010 07:07:53

Да, действительно. Лень не всегда двигает прогресс в нужном направлении

Непонятно только почему он для таблицы b пишет possible_keys: NULL.
Если обе таблицы в myisam, то explain такой же как у тебя, а если перевести в Innodb, то для первой таблицы
possible_keys: NULL
key: col1

Форум пользователей MySQL

#1 02.03.2010 02:15:46

Использование индексов при объединении таблиц

#2 02.03.2010 06:27:09

Re: Использование индексов при объединении таблиц

#3 02.03.2010 06:39:26

Re: Использование индексов при объединении таблиц

#4 02.03.2010 06:45:30

Re: Использование индексов при объединении таблиц

#5 02.03.2010 07:07:53

Re: Использование индексов при объединении таблиц

Board footer