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