EverSQL Database Performance Knowledge Base

Question 1

How to optimize query with multiple %

Database type:

Please help me optimize my Mysql query below

SELECT * FROM `ticket` WHERE `ticket_id` LIKE '%wm%33%' LIMIT 0,1000

the table doesn't have full text index and i'm affraid that giving the column a full text index will cost time when table is needed to be always on

Answer

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

Avoid LIKE Searches With Leading Wildcard (query line: 6): The database will not use an index when using like searches with a leading wildcard (e.g. '%wm%33%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
Avoid OFFSET In LIMIT Clause (query line: 6): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.

SELECT
        * 
    FROM
        `ticket` 
    WHERE
        `ticket`.`ticket_id` LIKE '%wm%33%' LIMIT 0, 1000

Question 2