CODEWITHSUNDEEP
mysqlsqlsql-likequery-performance
Biju P Dais
Biju P Dais

Reputation: 131

mysql like query is very slow in pulling results

I have a very large mysql database consisting of 35 tables. Each table is different in structure, but all have some common fields. 'entity_name' is one such common field in all the tables. It is a VARCHAR field. Some tables contains millions of records, while some contains billions.

I am searching for a search key in all these tables and pull the entity name, plus some of the common fields from the tables containing matching records; Push the results to an array and finally display in HTML. The queries are pretty straightforward:

SELECT * FROM table_name WHERE entity_name LIKE '%search_key%' //Partial match.

SELECT * FROM table_name WHERE entity_name LIKE 'search_key%' //Begins with.

SELECT * FROM table_name WHERE entity_name IN('search_key') //Equal to.

Most often the first query is used.

The size of the database is around 9GB. The search process is very slow. Per research I have found that using indexes might not working since I am searching with this: entity_name LIKE '%search_key% The wildcard char appears in the front. Partitions also do not work(please correct if I am wrong) since we are focusing on a varchar field.

Any ideas/ suggestions are welcome.

Upvotes: 0

Views: 4556

Answers (3)

DIGVJSS
DIGVJSS

Reputation: 499

If you are showing your records in a web page then I believe you may not need the whole bunch of data in single go. The best way for such huge amount of data is to perform pagination.

You can limit your query on number of records, might be 10, 25 or 50 at a time, which may not take much time to execute your query. Your query can be -

SELECT * FROM table_name WHERE entity_name LIKE '%search_key%' limit x,y;

Here replace x with your lower limit and y with your upper limit.

In case you need the count of your matching records use below query just to calculate count.

SELECT count(*) FROM table_name WHERE entity_name LIKE '%search_key%';

Hope this helps!

Upvotes: 0

Lyubetrix
Lyubetrix

Reputation: 51

If your search_key is dynamic (searching in text) it would be better to use different kind of DB. Something like elastic for example or other similar.

If your search_key could be defined as ENUM take it out in a different column and use it instead.

Searching in text is not the mySQL way.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108839

You are correct when you say LIKE '%search_key' is inherently slow. That kind of search term is not sargable because it begins with the %. Indexes on the entity_name column may still help a bit especially if your tables have many columns: MySQL may be able to scan the index rather than the table. But it will never be fast.

You should consider using MySQL FULLTEXT searching for this application.

Upvotes: 3

Related Questions