How to Optimize/Refactor a Tsql "Like" Clause

How can I optimize/refactor a TSQL LIKE clause?

Use Full Text Search and CONTAINS. LIKE cannot be optimized when searching in the middle of the field, ie. when the LIKE expression starts with an '%', so it will always do a full table scan.

How can I optimize/refactor the sql like order by column1 + column2

Order By on computed column slows down query.
Create a new column having values ReplyCount+OfficialReplyCount and create index on it and then use this column name in Order By.

How to refactor a SQL query with multiple OR statements for better performance?

MariaDB understands tuple equality, so you could write the conditions as:

where 
    id > 0 
    and (code_field, date_field) in (
        (:code1, date(:date1)),
        (:code2, date(:date2)),
        (:code3, date(:date3)),
        ...
        (:codeN, date(:dateN))
    )

This might take advantage of an index on (code_field, date_field, id).

Performance of like '%Query%' vs full text search CONTAINS query

Full Text Searching (using the CONTAINS) will be faster/more efficient than using LIKE with wildcarding. Full Text Searching (FTS) includes the ability to define Full Text Indexes, which FTS can use. I don't know why you wouldn't define a FTS index if you intended to use the functionality.

LIKE with wildcarding on the left side (IE: LIKE '%Search') can not use an index (assuming one exists for the column), guaranteeing a table scan. I haven't tested & compared, but regex has the same pitfall. To clarify, LIKE '%Search' and LIKE '%Search%' can not use an index; LIKE 'Search%' can use an index.

How to Optimize/Refactor MySQL Pivot Table Performance when using Where Clause

This should be a lot faster with large data sets. Plus it can easily be extended to any number of "virtual" fields. You can place any search criteria you may have between the %%.

select  
    i.id,  
    coalesce(max(case when field = 'organization' then value end), '') as organization,  
    coalesce(max(case when field = 'state' then value end), '') as state  
from t_id i  
left join t_data d  
on i.id = d.id  
and i.id like '%%'  
and i.id in (  
  select id  
  from `t_data`  
  where `field` = 'organization'   
  and `value` like '%%'  
  and  id  in (  
    select id  
    from `t_data`  
    where `field` = 'state'  
    and `value` like '%%'  
  )  
)  
group by i.id  

Execute Subquery refactoring first before any other SQL

Are these transformations really that complex you have to use UNION ALL? It's really hard to optimize something you can't see, but have you maybe tried getting rid of the CTE and implementing your calculations inline?

CREATE OR REPLACE VIEW loan_vw AS
SELECT loan.contract_id
     , CASE commission.type -- or wherever this comes from
         WHEN 'PRINCIPAL'
         THEN SUM(whatever) OVER (PARTITION BY loan.contract_id, loan.type) -- total_whatever

         ELSE SUM(something_else) OVER (PARTITION BY loan.contract_id, loan.type) -- total_something_else
      END AS whatever_something
  FROM loan_table loan 
 INNER 
  JOIN commission_table commission
    ON loan.contract_id = commission.commission_id

Note that if your analytic functions don't have PARTITION BY contract_id you won't be able to use an index on that contract_id column at all.

Take a look at this db fiddle (you'll have to click on ... on the last result table to expand the results). Here, the loan table has an indexed (PK) contract_id column, but also some_other_id that is also unique, but not indexed and the predicate on the outer query is still on contract_id. If you compare plans for partition by contract and partition by other id, you'll see that index is not used at all in the partition by other id plan: there's a TABLE ACCESS with FULL options on the loan table, as compared to INDEX - UNIQUE SCAN in partition by contract. That's obviously because the optimizer cannot resolve the relation between contract_id and some_other_id by its own, and so it'll need to run SUM or AVG over the entire window instead of limiting window row counts through index usage.

What you can also try - if you have a dimension table with those contracts - is to join it to your results and expose the contract_id from the dimension table instead of the most likely huge loan fact table. Sometimes this can lead to an improvement in cardinality estimates through the usage of a unique index on the dimension table.

Again, it's really hard to optimize a black box, without a query or even a plan, so we don't know what's going on. CTE or a subquery can get materialized unnecessarily for example.

---

Related Topics