mysql performance

Question

We are developing our database in MySql with innoDB engine. The database contains a column that is of varchar type with each entry containing about 3000 characters. We are to provide search on this column. For speeding up purpose, we need to add index on this column. Can you put in some information in this regard?

Which type of index do we need to put in to speed up the search? Do we need to take some other care about it for performance improvement?

Answer 1

If by search you mean you'll be performing a query like this:

SELECT * from cars WHERE car LIKE '%{search_str}%'

Then I am afraid that even if you add a key to the car column mysql will still have to perform a full-scan and your query might potentiolly be very slow.

If you are planning on supporting a significan amount of data to be searched and expect some high qps numbers, I would reccomend you have a look at Apaches Lucene project, which can drastically speed up any search query performed. Plus, it also supports full-text search.

Answer 2

Like ducky says, if you're going to query the column using a SQL LIKE, you're query is going to be very slow, no matter what index you put on the column.

There's 2 options:

• Switch to MyIsam database instead of InnoDB and use full-text search on this column. This is done by placing a 'fulltext' index on the column. More Information.
• Use a tool like Lucene for full-text searching