CODEWITHSUNDEEP
performancemariadbinnodb
kreya
kreya

Reputation: 1229

Performance Issue with LIKE Query on MariaDB (Synology)

I'm using MariaDB hosted on a Synology NAS. I have a query that's taking around 2.5 seconds, and I want to optimize its execution time. Here's the query:

SELECT * FROM jobs WHERE job_number LIKE '%Y2506195%'

Table Details:

Current Observations:

Buffer Pool Configuration Issue:

After making the changes and restarting the MariaDB server using the sudo command, the buffer pool size still shows as 16MB.

Questions:

  1. How can I optimize this LIKE query for better performance?
  2. Why is the innodb_buffer_pool_size not reflecting the changes after modifying the config file?

Any advice or suggestions are greatly appreciated!

Upvotes: 0

Views: 34

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

As @akina has mentioned in his comment, your current LIKE expression is non sargable, meaning it cannot use any index:

SELECT * FROM jobs WHERE job_number LIKE '%Y2506195%';

One option which you may consider here is to create and maintain a computed column which is true when the job_number contains the Y2506195 substring and false otherwise. To seed this column, use this update:

UPDATE jobs
SET job_flag = job_number LIKE '%Y2506195%';

Then index this flag:

CREATE INDEX idx ON jobs (job_flag);

Finally, your current query now becomes:

SELECT * FROM jobs WHERE job_flag;

This query should be very fast, even for millions of records.

Note that the drawback with this approach is that you need to maintain an additional column for the job_number. This means that any time you do an insert or update, you would have to update this computed column.

Upvotes: 0

Related Questions