Optimising a subquery on a FROM clause in MySQL

Question

I am developing a web application that has a listbox with thousands of records from a MySQL database ('Table1').

• I have a quick search field that searches all fields with the OR operator (value: aaaa).
• I have a dialog box that allows filtering by all fields with the AND operator (values: xxx, yyy, zzz).

The idea is to create a query that contains both, the filtered values and the search value. I have created a subquery on a FROM clause like this:

SELECT b.* 
  FROM 
     ( SELECT * 
         FROM Table1 
        WHERE Field1 LIKE '%xxx%' 
          AND Field2 LIKE '%yyy%' 
          AND Field3 LIKE '%zzz%' 
      ) b 
  WHERE b.Field1 LIKE '%aaaa%' 
     OR b.Field2 LIKE '%aaaa%' 
     OR b.Field3 LIKE '%aaaa%'

After running the subquery it seems to me that the performance is not optimal.

Would it be possible to improve the query in some way to optimize the performance (and lower the response time)?

Thank you very much.

Wardiam

Update:

It seems to me that it would be more correct to use a Common Table Expression (CTE). I have used this CTE expression:

WITH CTE_Expression AS
(SELECT *
FROM Table1
WHERE Field1 LIKE '%xxx%'
AND Field2 LIKE '%yyy%'
AND Field3 LIKE '%zzz%'
)
SELECT b.*
FROM CTE_Expression b
WHERE b.Field1 LIKE '%aaaa%'
OR b.Field2 LIKE '%aaaa%'
OR b.Field3 LIKE '%aaaa%'

What do you think?.

question from:https://stackoverflow.com/questions/65865513/optimising-a-subquery-on-a-from-clause-in-mysql

Answer 1

Found a similar issue:

Mysql Improve Search Performance with wildcards (%%)

No, because MySQL will not be able to utilize the index when you have a leading wildcard. If you changed your LIKE to 'aaaa%', then it would be able to use the index.

If you want to check if indices are being utilized, check the execution plan with EXPLAIN:

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

Or try using the MYSQL Full-Text Index MATCH()/AGAINST(). Here are some articles about:

https://severalnines.com/database-blog/full-text-searches-mysql-good-bad-and-ugly https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

Edit: After some investigation, I came to the conclusion that there is no way a leading wildcard can utilize the Table index

Wildcard search in MySQL full-text search https://blog.monyog.com/problem-queries-are-killing-your-database-performance/