Have Geek Brain

Mysql performance

We are developing our database in MySql using the innoDB engine. The database contains a varchar column with each record containing about 3000 characters. We must search this column. To speed up the goal, we need to add an index to this column. Can you add some information in this regard?

What type of index should I add to speed up searches? Do we need to care a little about this to improve performance?

0


a source to share


2 answers


If on demand you want to say that you will run a query like this:

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


Then I am afraid that even if you add the key to the column car

, then mysql will still have to do a full scan and your query may slow down a lot.

If you are planning to maintain a significant amount of data to search for and expect high qps numbers, I would recommend that you take a look at Apaches Lucene , which can speed up any search query significantly. Moreover, it also supports full text search.

+3


a source


As ducky says, if you are going to query the column using SQL LIKE, the query will be very slow no matter what index you put on the column.

There are 2 options:



  • Go to MyIsam database instead of InnoDB and use full text search on that column. This is done by placing a full text index on the column. Additional Information.
  • Use Lucene's Full Text Search Tool
+1


a source






More articles:


All Articles