1

Consider the following table:

CREATE TABLE `item` (
 `id`  bigint(11) unsigned NOT NULL AUTO_INCREMENT,
 `category` smallint(11) unsigned NOT NULL,
 `description` TEXT(500) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `category` (`category`),
 FULLTEXT KEY `fulltextnameindex` (`description`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `item` (`id`, `category`, `description`)
 VALUES
(1, 1, 'Nothing here for you'),
(2, 2, 'Still nothing here for you'),
(3, 2, 'this is sometext for you');

To perform a fulltext search to match the string sometext in the description field within a category I run the following:

SELECT i.id FROM item i 
  WHERE MATCH(i.description) AGAINST ('+sometext*' IN BOOLEAN MODE) AND category IN (2);

This works great, until the table gets huge (especially since I need to have the minimum indexed word token size to be 1 or 2).

  1. There are many categories, and the distribution of rows is fairly equal across categories
  2. With non-FULLLTEXT indexes you can create composite indexes that would make this more efficient like CREATE INDEX yippie ON item(category,name) would work if you were doing something like SELECT i.id FROM item i WHERE category IN (2) AND name LIKE '%sometext% (note, I'm not claiming '%sometext%' is efficient in itself, but the query more efficient with the category where clause due to the composite coving index).
  3. The dream would be to have some kind of composite index across both the category and name column like: CREATE FULLTEXT INDEX yippie ON item(category,name), but this of course isn't valid

Does anyone know a way to achieve the effect of #3 using FULLTEXT search (without creating separate tables for each category)?

Ray
  • 40,256
  • 21
  • 101
  • 138

1 Answers1

0

ALTER TABLE ADD FULLTEXT(category, name);

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

Note (in InnoDB) that if you are doing both

MATCH (category) ...
MATCH (category, name)

then you need both 

FULLTEXT(category)
FULLTEXT(category, name)

If you do change ft_min_token_size, remember to rebuild all FULLTEXT indexes. See innodb_optimize_fulltext_only.

name LIKE '%some%' is very slow because it will scan every name in every row.

If category is a number, do not put it in any FULLTEXT index. category IN (2) AND MATCH... will first do the FULLTEXT search, then further filter by `category. This should be adequately efficient.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • `category` is an integer foreign key. If you do a `category IN (2) AND MATCH..` your still doing a match, then a full scan of the match result to filter the `category`. In my case this result set is pretty huge, so I'm looking to filter by category BEFORE doing the full text search or after if it was part of the FT index--this is what seems to be impossible – Ray Aug 21 '15 at 13:23
  • I think it is impossible to trick it into using `INDEX(categoery)` before `MATCH...` – Rick James Aug 21 '15 at 22:23
  • I agree, I think MySQL's fulltext indexes are limited and since only a single can be used on a table, if fulltext index is the one that's picked, no further optimization can be done. – Ray Aug 23 '15 at 19:22