CODEWITHSUNDEEP
mysqlperformancejoin
Hacker
Hacker

Reputation: 7896

Performance issue when using LIKE in joins

We have a ecommerce website. We give creditvouchers to customer. He can use this in 1,2...n orders which he places further. Say We issued creditvoucher called 'abcd'. When he splits and uses the voucher and uses in order, it will come in adjustment like abcd-1 etc. So i am trying to make a report which lists all creditvouchers issued and orders placed with these vouchers. I wrote the below query. It works fine, but it takes lot of time to load. Any thing i can do for performance?

SELECT *
FROM (creditvouchers)
LEFT JOIN adjustment ON `adjustment`.`code` 
                    LIKE IF((creditvouchers.creditvoucher = ''),
                        creditvouchers.creditvoucher,
                        CONCAT(creditvouchers.creditvoucher,'','%'))

Here are the tables

creditvouchers -> id PK, creditvoucher varchar(50)
adjustment     -> id PK, code varchar(50)

Upvotes: 0

Views: 65

Answers (2)

Olli
Olli

Reputation: 1738

I also would suggest that you use an numeric id for your voucher and another table where you have the mappings between a voucher and your orders. in this mapping table you would only have ids to match against which is far faster than varchar comparison with LIKE.

If you would like to know what adjustments you have, you simply search for the voucher in your table and join it with the voucher_id in the mapping table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Here is the logic of your query without the if():

SELECT *
FROM creditvouchers cv LEFT JOIN
     adjustment a
     ON a.`code` like concat(cv.creditvoucher, '%') and cv.creditvoucher <> '' or
        a.code = '';

MySQL is very bad about optimizing joins with or in the on clause. However, the real challenge is this part:

select *
from creditvouchers cv left join
     adjustment a
     on a.code like concat(cv.creditvoucher, '%') and cv.creditvoucher <> ''

I would suggest that you think about your data structure. I suspect that an intermediate table, CreditvoucherCode would help your system. This would have one row for each credit voucher and code.

Another possibility is that if each creditvoucher has the same length, say 4, then you can add another field to adjustment that contains the creditvoucher.

Upvotes: 1

Related Questions