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

FiMko · 01.05.2013 15:44:44

Всем привет!

Не могли бы вы подсказать, не могу сообразить следующее. Имеется две таблицы:

mysql> show create table words;

-- содержит слова (складываются во фразы)

CREATE TABLE `words` (

    `wordid` int(10) unsigned NOT NULL AUTO_INCREMENT,

    `word` varchar(70) COLLATE utf8_bin NOT NULL,

    PRIMARY KEY (`wordid`),

    UNIQUE KEY `word` (`word`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

mysql> show create table phrases;

-- содержит фразы (последовательности слов)

CREATE TABLE `phrases` (

    `phraseid` int(10) unsigned NOT NULL,

    `wordid` int(10) unsigned NOT NULL,

    `wordorder` int(10) unsigned NOT NULL,

    KEY `wordid` (`wordid`),

    KEY `phraseid` (`phraseid`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Хочу найти все фразы, содержащие заданные слова:

EXPLAIN SELECT DISTINCT phrases.phraseid

    FROM phrases

    JOIN words

    ON words.word COLLATE utf8_general_ci IN ('test','phrase')

    WHERE phrases.wordid = words.wordid;

+---------+-------+-----------------+----------+---------+-------+

| table   | type  | possible_keys   | key      | key_len | rows  |

+---------+-------+-----------------+----------+---------+--------

| words   | index | PRIMARY         | word     | 212     | 80125 |

| phrases | ref   | wordid          | wordid   | 4       |     2 |

+---------+-------+-----------------+----------+---------+-------+

Вопрос: почему количество строк для words 80125, ожидаю всего две ('test','phrase'). Из-за того, что INNER JOIN возвращает каждую строку, удовлетворяющую выражению в части ON для обеих таблиц и поскольку в части условия ON не фигурирует таблица phrases? Как можно поправить эту ситуацию?

Заранее брагодарен за любую помощь!

Отредактированно FiMko (01.05.2013 16:16:40)

rgbeast · 01.05.2013 15:48:14

Условие, не связывающее таблицы, разумнее перенести в WHERE, а связывающее - в ON. Так будет лучше?

EXPLAIN SELECT DISTINCT phrases.phraseid

    FROM phrases

    JOIN words

    ON phrases.wordid = words.wordid

    WHERE words.word COLLATE utf8_general_ci IN ('test','phrase');

FiMko · 01.05.2013 16:08:31

rgbeast написал:
Условие, не связывающее таблицы, разумнее перенести в WHERE, а связывающее - в ON. Так будет лучше?

rgbeast, спасибо за помощь! Попробовал, результат тот же - 80125.
Остаются, конечно, prepared statements, но я не теряю надежды найти способ как это лечить на уровне запроса.

--- добавлено ---

Что-то я совсем не понимаю:

mysql> select count(*) from words;

+----------+

| count(*) |

+----------+

|    79756 |

mysql> explain SELECT wordid from words WHERE word COLLATE utf8_general_ci IN ('test','phrase');

+---------+-------+-----------------+----------+---------+-------+

| table   | type  | possible_keys   | key      | key_len | rows  |

+---------+-------+-----------------+----------+---------+--------

| words   | index | PRIMARY         | word     | 212     | 80125 |

+---------+-------+-----------------+----------+---------+-------+

Строк в таблице даже фактически меньше...

--- добавлено ---

Однако (без COLLATE utf8_general_ci):

mysql> explain SELECT wordid from words WHERE word IN ('test','phrase');

+----+-------+---------------+------+---------+------+------+

| id | type  | possible_keys | key  | key_len | ref  | rows |

+----+-------+---------------+------+---------+------+------+

|  1 | range | word          | word | 212     | NULL |    2 |

+----+-------+---------------+------+---------+------+------+

Отредактированно FiMko (01.05.2013 16:38:49)

rgbeast · 01.05.2013 16:51:40

COLLATE utf8_general_ci - это преобразование, которое не позволяет использовать индекс. EXPLAIN дает приблизительное число строк, поэтому может быть и больше, чем в таблице.

FiMko · 01.05.2013 17:57:21

rgbeast написал:
COLLATE utf8_general_ci - это преобразование, которое не позволяет использовать индекс. EXPLAIN дает приблизительное число строк, поэтому может быть и больше, чем в таблице.

Вот это для меня новость! Тогда есть проблема другого рода:

В таблице words не должно быть дубликатов, при этом дубликатами являются только одинаковые слова в том же регистре (напр. "Test" и "Test" - дубликаты, а "Test" и "test" - нет). Контролировать подобное хотелось на уровне схемы базы, не в логике. CHARACTER SET utf8 COLLATE utf8_bin для таблицы words в паре с UNIQUE KEY на колонку words.word позволяли решить эту задачу.

Теперь, если для таблицы words поставить CHARACTER SET utf8 COLLATE utf8_general_ci и оставить UNIQUE KEY на колонку words.word, слова "test" и "Test" будут считаться дубликатами при добавлении. Если убрать ключ UNIQUE KEY, то надо как-то контролировать уникальность добавляемых слов. Триггер?

Можете посоветовать какое-то приемлемое решение?

DELIMITER //

DROP TRIGGER IF EXISTS words_beforeinsert; //

CREATE TRIGGER words_beforeinsert

BEFORE INSERT ON words

FOR EACH ROW

BEGIN

    SELECT TRUE

    INTO   @is_duplicate

    FROM   test

    WHERE  word COLLATE utf8_bin = NEW.word;

    IF @is_duplicate THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'duplicate insert';

    END IF;

END; //

DELIMITER ;

Отредактированно FiMko (01.05.2013 18:11:53)

rgbeast · 01.05.2013 18:25:20

Если слов немного, можно, например, хранить в двух экземплярах - CS и CI

FiMko · 01.05.2013 18:26:28

rgbeast написал:
Если слов немного, можно, например, хранить в двух экземплярах - CS и CI

Очень много слов должно быть в перспективе (миллионы). Но вставка достаточно редкое явление, в основном - чтение.

rgbeast · 01.05.2013 18:27:57

Значит проверять уникальность триггером или на уровне приложения

FiMko · 01.05.2013 18:28:37

rgbeast написал:
Значит проверять уникальность триггером или на уровне приложения

Супер! Большое спасибо за помощь и подробное объяснение!

FiMko · 01.05.2013 21:33:28

rgbeast написал:
Значит проверять уникальность триггером или на уровне приложения

К сожалению, если проверять уникальность триггером, упираемся в ограничение - Trigger to silently ignore/delete duplicate entries on INSERT. Невозможно, как в случае с использованием UNIQUE KEY, silently осуществить INSERT IGNORE INTO table... у которой есть триггер, прерывающий вставку. В случае с триггером в любом случае будет сгенерирована ошибка. Похоже придется лезть и менять что-то на уровне приложения, а так не хотелось

--- добавлено ---

В итоге обернул add_word в хранимую процедуру со всеми необходимыми проверками.

Отредактированно FiMko (04.05.2013 16:16:25)

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

#1 01.05.2013 15:44:44

INNER JOIN, слишком много строк в EXPLAIN

#2 01.05.2013 15:48:14

Re: INNER JOIN, слишком много строк в EXPLAIN

#3 01.05.2013 16:08:31

Re: INNER JOIN, слишком много строк в EXPLAIN

rgbeast написал:

#4 01.05.2013 16:51:40

Re: INNER JOIN, слишком много строк в EXPLAIN

#5 01.05.2013 17:57:21

Re: INNER JOIN, слишком много строк в EXPLAIN

rgbeast написал:

#6 01.05.2013 18:25:20

Re: INNER JOIN, слишком много строк в EXPLAIN

#7 01.05.2013 18:26:28

Re: INNER JOIN, слишком много строк в EXPLAIN

rgbeast написал:

#8 01.05.2013 18:27:57

Re: INNER JOIN, слишком много строк в EXPLAIN

#9 01.05.2013 18:28:37

Re: INNER JOIN, слишком много строк в EXPLAIN

rgbeast написал:

#10 01.05.2013 21:33:28

Re: INNER JOIN, слишком много строк в EXPLAIN

rgbeast написал:

Board footer