Mysql – Like ‘%’ search for value withing longblob column in MySql is slow


MySQL version is 5.7.25

I have table like

CREATE TABLE `applications` (
  `id` varchar(25) NOT NULL,
  `application` longblob NOT NULL,
  PRIMARY KEY (`id`)

'application' is approximately 50kb xml text.

the table contains around 120k records.

When I am doing search

Select * from `applications`
application like '%<l:name>8085%</l:name>%'

the query fail by 30 minutes timeout.

if I do

Select A.* from (
Select * from `applications`
Limit 0,1000
)A where 
A.application like '%<l:name>8085%</l:name>%'

the query finished in 6 seconds.

so by doing pagination I can get the query result in 120*6 = 720 seconds

Interesting that same query with Limit 0,10000 finished in 120 seconds, with Limit 0,100000 failed by 30 minutes timeout

from this SO post I understand that probably longblob query performance is not linear from number of rows.

I finally finished with getting query results by doing pagination and concatenating results.

Select from (
Select * from `applications`
Limit 0,10000
)A where 
A.application like '%<l:name>8085%</l:name>%'

Select from (
Select * from `applications`
Limit 10000,20000
)A where 
A.application like '%<l:name>8085%</l:name>%'

The question is how to get results more convenient way.

The search string varies and is unknown in advance, updating column type to TEXT is not an option right now, since that would require application refactoring that no one wants at this point. The query is part of manual update, so the execution time is not really a problem.


Beside the point, but even though no one is updating the table, you may miss certain rows. You need to do an order by together with limit , if you want to be guaranteed to examine all rows.

Adding ORDER BY and checking for updates make sense, thank you.

Best Answer

The problem was finally fixed by Increasing the INNODB_BUFFER_POOL_SIZE from 256M to 1G After that the query run less than second.