More than ten million rows, painfully slow. Currently using 'LIKE' to count the number of hits:

SELECT COUNT(*) FROM `table` WHERE `job` LIKE '%sales%' AND `location` LIKE '%New York%'

Am I correct in thinking that I want to:

(1) Apply FULLTEXT index to each field

(2) Use a CONTAINS statement to increase speed:

SELECT COUNT(*) FROM `table` WHERE contains('location', '"New York"') and contains ('job',"sales")

Would this produce identical results to my initial LIKE query?

Would this be many times faster, with the only downside being a huge increase in the storage size of the database?

Example: I want to count data like this: "sales manager", "Buffalo, New York"

CodePudding user response：

Add this index to your table:

FULLTEXT (location, job)

The use this WHERE clause:

WHERE MATCH(location, job) AGAINST("sales manager new york")

It will work immensely faster.

More

If you have lots of columns with "text", and the search may include stuff from any or all of them, it may be better to add an extra column with all the text lumped together. Then have a FULLTEXT index on just that column and MATCH against just that column.

Be aware that FULLTEXT does not handles numbers or inequalities. Nor does it handle "short" words or "stop "words.

CodePudding user response：

Q: Am I correct in thinking that I want to apply FULLTEXT index to each field and replace the query with something like this to increase speed?

A: Yes, you're correct at adding index to make improve the performance.

Q: Am I correct in thinking that this would produce identical results to my LIKE query?

A: I'm not sure cause I couldn't find any clause related to CONTAINS in MySQL. But please refer this as a solution for full-text indexs.

Q: Am I correct in thinking that this would be many times faster, with the only downside being a huge increase in the storage size of the database?

A: Well, honestly speaking, it is not a good idea. Because text is unpredictable, it is not a good idea using text as index.

You might choose already your mind regardless of my thinking. But I hope you find another column for lower risk and lower cost to indexing. Thanks.