CODEWITHSUNDEEP
mysqlindexingfull-text-searchlimit
user3746280
user3746280

Reputation:

MySQL fulltext inadequate. Creating an index would exceed 1000 bytes limit. What to do then?

Here are the columns involved:

order_id int(8)

tracking_number char(13)

carrier_status varchar(128)

recipient_name varchar(128)

recipient_address varchar(256)

recipient_state varchar(32)

recipient_country_descr varchar(128)

recipient_zipcode varchar(32)

Getting this error when I try to create an index of all these columns:

MySQL #1071 - Specified key was too long; max key length is 1000 bytes

What can I do given that this index is very important for the database?

I need to allow users to search all fields from a form with a single search field. So I'm using this query:

WHERE CONCAT(tracking_number, recipient_name, recipient_address, recipient_state, recipient_country_descr, recipient_zipcode, order_id, carrier_status) LIKE '$keyword1'

I've also considered using fulltext match() against(). Problem was that it doesn't allow searches like *keyword so I scratched it and am doing it with simple LIKE %keyword1% AND LIKE %keyword2% for each keyword.

But now I've ran into another problem, the query may be slow as I cannot create a single index containing all columns that will be searched.

What to do in this situation?

Upvotes: 1

Views: 118

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

A conventional B-tree index cannot help when you're searching for a keyword that may be in multiple columns, or may be in the middle of a string.

Think of a telephone book. It's like an index on (last name, first name). If I ask you to search for a certain last name, it helps a lot that the book is already sorted that way. If I ask you to search for a specific last name and first name combination, the sort order of the book is also helpful.

But if I ask you to search for someone only by a specific first name "Bill", the fact that the book is sorted is not helpful. Occurrences of "Bill" could be found anywhere in the book, so you basically have to read it cover-to-cover.

Likewise if I ask you to search for anyone's name that contains a certain substring in the middle of the name or at the end. For example, anyone's last name that ends in "-son".

Your example of using CONCAT() over a bunch of columns and comparing that to a keyword in a LIKE pattern has the same problem.

The solution is to use a fulltext search engine, which does offer the ability to search for words anywhere in the middle of strings. It indexes in a completely different way than the one-dimensional B-tree sorting.

If you don't find that MySQL's FULLTEXT index is flexible enough (and I wouldn't blame you because MySQL's implementation is pretty rudimentary), then I suggest you look at a more specialized search technology. Here are a few free software options:

This may mean that you have to copy the searchable text from MySQL to the search engine, and keep copying it incrementally as changes are made to your MySQL data. A lot of developers do this.

Upvotes: 1

Related Questions