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

MySQLstring-searching

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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


'application' is approximately 50kb xml text.

the table contains around 120k records.

When I am doing search

Select * from applications
where
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 A.id from (
Select * from applications
Limit 0,10000
)A where
A.application like '%<l:name>8085%</l:name>%'

Select A.id 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.