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?.

'%....' cannot use an index, so without a radical re-think, you're a bit stuck — Strawberry, Jan 23 '21 at 23:39
Yea, the LIKE '%...' at the start doesn't utilize the index :D — , Jan 23 '21 at 23:55
Strawberry, you are right. Everyone talking about this tells us to stay away from leading wildcards. It forces MySQL to perform a full table scan — , Jan 24 '21 at 00:18
I think your sub-query in the first query subverts the system optimization. I think if you want a sensible answer here you should should post questions which accurately describe a real scenario. I think that if you want help optimizing a query you should read the many questions on Stack Overflow about query optimization and note how those which are answered include table and index structures and the output of EXPLAIN plans. — symcbean, Jan 24 '21 at 00:23
I am using a view to make the query. On a view I can't use Full-Text index. — Wardiam, Jan 24 '21 at 00:41

score 0 · Answer 1 · 2021-01-24T00:20:19.060

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/

Still, `FULLTEXT` may be very useful -- if `xxx` and `yyy`, etc are really "words", not just arbitrary substrings. — Rick James, Jan 25 '21 at 00:53

Optimising a subquery on a FROM clause in MySQL

1 Answers1