optimise like `%value%` on join in exponential-growing, mysql database

Question

i tried lots of thing but not of them worked hope someone may help me with this query

let me show my query first then issue

select log.*,client.client_name
from ( select * from sessions  
        where   ( `report_error_status` like  CONCAT('%' ,'consec', '%')   
                    or `ipaddress` like  CONCAT('%' ,'consec', '%')  or `last_updated` like  CONCAT('%' ,'consec', '%')  ) 
                    ORDER BY `id`  DESC  LIMIT 10 OFFSET 0   )
log
inner join 
            (select * from clients  
                where  ( `client_name` like  CONCAT('%' ,'consec', '%')  ) ) 
client on log.client_id = client.id 

in order to prevent exponential reducing query speed i'm applying limit in my table session above query working perfectly fine without "where", but my problem lies over here if user from front end try to search any thing in datatable , where clause is dynamically get attached in backend (above query with where) now my problem is that suppose table (session) does not contain user search value consec ,but table (client) contain then final query still return null value now is there any way to apply conditional where like below query

   ifnull((select id from sessions  where 
    (`report_error_status` like  CONCAT('%' ,'consec', '%')   
               or `ipaddress` like  CONCAT('%' ,'consec', '%')  
               or `last_updated` like  CONCAT('%' ,'consec', '%'))
               ),
             (select * from sessions  ORDER BY `id`  DESC  LIMIT 10 OFFSET 0) ))

it will resolve all my problem is there any way to achieve in mysql. if table session contain 100 000 data it will search with client table one by one against 100k records. suppose time taken to execute is 1 sec now what if my session table has 200k data again time will increase exponentially in inner join, to avoid this i'm using subquery in session with limit

Note report_error_status,ipaddress, client_name etc in index

Answer 1

There is no way to optimize a MySQL SELECT statement that use a regex opening with the wildcard. Your REGEX is %consec%, and you could add an index, but to quote the official MySQL documentation...

The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

Source: Dev.MySQL.com: Comparison of B-Tree and Hash Indexes; B-Tree Index Characteristics

Your query falls outside of this use case, so indices will not help. Here's another answer suggesting the same.

I am going to suggest Database Normalization...

You're selecting fields that are LIKE %consec%. Why? What is this value? Is it a special, internal code that means something special for your software and your software alone? After all, look the names of the fields -- report_error_status, ipaddress, last_updated. Except for maybe the error code one, there's no reason "consec" would appear in these, unless it had some internal significance.

For instance, table.field has value of "userconsec", sometimes you want to search for "user", other times "consec".

In that case, you'd want a new table; "tableType", with tableType.tableid pointing to the other table and tableType.Type being the Type value ("user", "consec", etc.), an index on both tableid and Type, and then you can drop from your query WHERE LIKE ... and add instead JOIN ON tableType.tableid = table.id AND tableType.Type = "consec";.

It will be faster because...

• It is not looking through all the text of several text fields.
• It is looking through an ordered list of integers to identify the record you need.