MySQL Forums
Forum List  »  Russian

странное поведение JOIN
Posted by: Stepan Kuntso
Date: April 27, 2008 01:51PM

имеется следующая структура базы:
CREATE TABLE `TextObjects` (
  `objectId` int(10) unsigned NOT NULL,
  `value` VARCHAR(5119) NOT NULL,
  PRIMARY KEY  (`objectId`),
  KEY simpleIndex (value(20)),
  FULLTEXT KEY `fulltextIndex` (`value`)
)
ENGINE=MyISAM
DEFAULT CHARSET=UTF8
COLLATE = utf8_general_ci
COMMENT='текстовые объекты';


CREATE TABLE WordsTable
(
    wordId INT UNSIGNED NOT NULL AUTO_INCREMENT,
    word VARCHAR(511) NOT NULL,
    PRIMARY KEY (wordId),
    KEY (word(20))
) ENGINE=MyISAM
DEFAULT CHARSET = UTF8
COLLATE = utf8_general_ci
COMMENT = 'таблица нормальных форм всех слов, которые есть в тексте';

DROP TABLE IF EXISTS TextObjectsNormalIndex;
CREATE TABLE TextObjectsNormalIndex
(
    textIndexId INT UNSIGNED NOT NULL AUTO_INCREMENT,
    wordId INT UNSIGNED NOT NULL,
    textObjectId INT UNSIGNED NOT NULL,
    formId INT(3) UNSIGNED NOT NULL,
    valuePart FLOAT NOT NULL,
    PRIMARY KEY (textIndexId),
    KEY (wordId),
    KEY (textObjectId)
) ENGINE=MyISAM
DEFAULT CHARSET = UTF8
COLLATE = utf8_general_ci
COMMENT = 'индекс по словам в нормальных формах для текстовых атрибутов';


Вот этот запрос работает достаточно для меня быстро - 0.1 секунду. Недостаток - мне бы хотелось в нем же выбираеть еще соответствующие поля value из TextObjects
explain SELECT
    ->     TextObjectsNormalIndex.textObjectId
    -> ,   sum(TextObjectsNormalIndex.valuePart) AS valuePartSum
    -> ,   count(TextObjectsNormalIndex.textObjectId) as cnt
    -> FROM
    -> TextObjectsNormalIndex
    -> , WordsTable
    -> WHERE
    -> (WordsTable.word IN ('Флаг', 'Великобритания'))
    -> AND (WordsTable.wordId = TextObjectsNormalIndex.wordId)
    -> GROUP BY
    -> TextObjectsNormalIndex.textObjectId
    -> ORDER BY
    -> valuePartSum DESC
    -> LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: WordsTable
         type: range
possible_keys: PRIMARY,word
          key: word
      key_len: 62
          ref: NULL
         rows: 7
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: TextObjectsNormalIndex
         type: ref
possible_keys: wordId
          key: wordId
      key_len: 4
          ref: WikiFacts1.WordsTable.wordId
         rows: 12
        Extra:
2 rows in set (0.00 sec)


Вот этот запрос поидее должнен выбирать все, что мне надо. В принципе, он выбирает, но работает в 10 раз дольше - в среднем где-то 1.5 секунды, что уже неприемлимо.
explain SELECT
    ->     TextObjectsNormalIndex.textObjectId
    -> ,   sum(TextObjectsNormalIndex.valuePart) AS valuePartSum
    -> ,   count( TextObjects.objectId ) AS cnt
    -> ,   TextObjects.value
    -> FROM
    -> TextObjectsNormalIndex
    -> , TextObjects
    -> , WordsTable
    -> WHERE
    -> (WordsTable.word IN ('Флаг', 'Великобритания'))
    -> AND (WordsTable.wordId = TextObjectsNormalIndex.wordId)
    -> AND (TextObjects.objectId = TextObjectsNormalIndex.textObjectId)
    -> GROUP BY
    -> TextObjectsNormalIndex.textObjectId
    -> ORDER BY
    -> valuePartSum DESC
    -> LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: WordsTable
         type: range
possible_keys: PRIMARY,word
          key: word
      key_len: 62
          ref: NULL
         rows: 7
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: TextObjectsNormalIndex
         type: ref
possible_keys: wordId,textObjectId
          key: wordId
      key_len: 4
          ref: WikiFacts1.WordsTable.wordId
         rows: 12
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: TextObjects
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: WikiFacts1.TextObjectsNormalIndex.textObjectId
         rows: 1
        Extra:
3 rows in set (0.00 sec)

Объем данных:
WordsTable; 223334
TextObjects; 385243
TextObjectsNormalIndex; 2019213


смысл всего этого дела.
есть таблица TextObjects, в которой содержатся некоторые строчки текста
со своими id: objectId - идентификатор, value - этот самый текст.

я делаю по всему этому делу индекс следующим образом. Для каждой строки
разбиваю ее на слова, для каждого слова нахожу нормальную форму. Эту
нормальную форму вставляю в таблицу WordsTable (wordId, word). Все слова вставленные слова уникальны.

После этого для каждого текстового объекта и для каждого слова в этом тексте вставляю в таблицу TextObjectsNormalIndex
запипись, содержащую идентфикатор objectId и идентификатор wordId нормальной
формы этого слова.

Теперь поиск. мне надо выбрать все идентификаторы объектов (objectId), которые
максимально близки к моему тексту запроса. как я это делаю. Разбиваю запрос
на слова, нахожу идентификаторы нормальных форм. После этого выбираю все
объект, в которых встречаются эти слова. Так получается, если не использовать
группировку.

Группировка. Помимо всего, о чем я рассказал, у меня есть поле valuePart, которое является
числом от 0 до 1 и равно отношению длины слова к длине всего текста.
Следовательно, задача сводится к следующей - найти все объекты, в которых
встречаются слова из запроса. Далее посчитать для них суммарный valuePart по
всем найденным словам, отсортировать в порядке
убывания и взять верхние, предположим, 10 записей.

Options: ReplyQuote


Subject
Views
Written By
Posted
странное поведение JOIN
5664
April 27, 2008 01:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.