имеется следующая структура базы:
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 записей.