MySQL Longblob Column Search – How to Optimize LIKE ‘%’ Queries

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.

lennart:

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.