将用户输入的标题与类别匹配 - 大型INNODB数据库

Question

I have a large INNODB database with over 2 million products on it. The 'products' table has the following fields: id,title,description,category.

There is also a MyISAM table called 'category' that contains a list of all categories used on the website. This has the following fields: id,name,keywords,parentid.

My question is more about the logic rather than code, but what I am trying to achieve is as follows:

When a user lists a new product on the site, as they are typing the description it should try to work out what category to put the product in (with good accuracy).

I tried this initially by using MySQL MATCH() to match the entered title against a list of keywords in the category table, but this was far from accurate.

A better idea seems to be to match the user entered title against titles for products already in the database, grouping them by the category they are in and then sorting them by the largest group. However, on an INNODB database I obviously can't use fulltext, and with 2mill items I think it would be pretty slow anyway?

How would you do it - I guess it would need to be a similar way to how stackoverflow displays similar questions?

Answer 1

A fulltext index on 2 million records is a valid option, if you are running on a decent server. The inital indexing will take a while, that's for sure, but searches should be reasonably fast, MySQL can take it.

InnoDB supports fulltext indexes as of v5.6.4. You should consider upgrading.

If upgrading is not an option, please see this previous answer of mine where I suggest a workaround.

For your use case, you may want to take a look at the WITH QUERY EXPANSION option:

It works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search. Thus, if one of these documents contains the word “databases” and the word “MySQL”, the second search finds the documents that contain the word “MySQL” even if they do not contain the word “database”