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

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

here 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 error when seek create index of these columns:

mysql #1071 - specified key long; max key length 1000 bytes

what can given index of import database?

i need allow users search fields form single search field. i'm using query:

where concat(tracking_number, recipient_name, recipient_address, recipient_state, recipient_country_descr, recipient_zipcode, order_id, carrier_status) '$keyword1'

i've considered using fulltext match() against(). problem doesn't allow searches *keyword scratched , doing simple %keyword1% , %keyword2% each keyword.

but i've ran problem, query may slow cannot create single index containing columns searched.

what in situation?

a conventional b-tree index cannot help when you're searching keyword may in multiple columns, or may in middle of string.

think of telephone book. it's index on (last name, first name). if inquire search lastly name, helps lot book sorted way. if inquire search specific lastly name , first name combination, sort order of book helpful.

but if inquire search specific first name "bill", fact book sorted not helpful. occurrences of "bill" found anywhere in book, have read cover-to-cover.

likewise if inquire search anyone's name contains substring in middle of name or @ end. example, anyone's lastly name ends in "-son".

your illustration of using concat() on bunch of columns , comparing keyword in like pattern has same problem.

the solution utilize fulltext search engine, does offer ability search words anywhere in middle of strings. indexes in different way one-dimensional b-tree sorting.

if don't find mysql's fulltext index flexible plenty (and wouldn't blame because mysql's implementation pretty rudimentary), suggest @ more specialized search technology. here few free software options:

apache solr sphinx search xapian

this may mean have re-create searchable text mysql search engine, , maintain copying incrementally changes made mysql data. lot of developers this.

mysql indexing full-text-search limit