For the query above, the following recommendations will be helpful as part of the 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.
The optimization process and recommendations:
Avoid Calling Functions With Indexed Columns (query line: 6): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `columnname` is indexed, the index won’t be used as it’s wrapped with the function `lower`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
Use Equality Operator Over LIKE (modified query below): Equality operators (such as '\u003d') are usually better optimized and more readable. Prefer the equality operator when searching for a constant value such as `myString`.
The optimized query:
lower(yourtable.columnname) = 'myString'