How to optimize sql in large list?

I have a sql as shown below.

SELECT * FROM payment
WHERE is_deleted = 0
    AND is_privacy = 0
    AND ou_code IN ('A1', 'A2', ..., 'W1', 'W2'...)
LIMIT 0, 20

There are many elements after in, about 3,000.payment table has 50 million data. I tried the following methods:

1、use a temporary table: create a new temporary table, insert the ou_code list into the table and create an index, and then use inner join. But my Java application is prohibited from using DDL statements in the program, so I can’t try this method.

2、 So I tried to create a temporary table with sql syntax: like below:

SELECT * FROM 
(VALUES ROW('A1'), ROW('A2')... ROW('W1'), ROW('W2')...) tem(`code`)
inner join 
payment
on payment.`ou_code` =  tem.`code`
WHERE is_deleted = 0
    AND is_privacy = 0
LIMIT 0, 20

But this table doesn’t seem to be able to create an index, so the efficiency is not improved, but it’s slower.

3、use exists: I tried it, but this method cannot create an index either and is even slower.

SELECT *
FROM 
payment
WHERE is_deleted = 0
    AND is_privacy = 0
    AND exists (select 1 from (SELECT 'WA1' AS `code`
UNION ALL  
SELECT 'WA2'  
UNION ALL  
SELECT 'WA3') tem where tem.code = payment.ou_code)
LIMIT 0, 20

So is there any other better way?

You need to sign in to view this answers

Related Post