How can I speed up this query?
I have a table with 60 attributes in it, attribute1..attribute60. The database engine in MySQL and the table engine is MyISAM. The query is as follows:
SELECT DISTINCT attribute1
FROM `product_applications`
WHERE `product_applications`.`brand_id` NOT IN (642, 630, 513, 637, 632,
556, 548, 628, 651, 660,
648, 557, 650, 624, 652,
636, 546, 662, 634, 629,
657, 638, 658, 659, 661, 625)
I use a NOT IN
, because that list is significantly smaller than the IN list.
I have created the following index:
brand_id, attribute1, attribute2, attribute3, attribute4
A DESC reveals that this index is being selected, but it looks like it is still looking at the whole table because I see the whole row count in the "rows" column:
6732948
In the "extra" column I have:
Using where; Using index; Using temporary
This query is taking over 7 seconds. I am looking at all different options here, including breaking the table up.
UPDATE:
I was able to cut the query time in half with the clever use of the UNION ALL noted by my friend below. Also, this is a dynamically genera开发者_Python百科ted query, so none of the temporary table options that some of you have offered, while an excellent idea, were available to me.
Previously, the following used a LEFT JOIN -- but the OP reversed the logic to use an INNER JOIN:
SELECT DISTINCT
t.attribute1
FROM PRODUCT_APPLICATIONS t
JOIN (SELECT 642 AS brand_id
UNION ALL
SELECT 630
UNION ALL
SELECT 513
UNION ALL
SELECT 637
UNION ALL
SELECT 632
UNION ALL
SELECT 556
UNION ALL
SELECT 548
UNION ALL
SELECT 628
UNION ALL
SELECT 651
UNION ALL
SELECT 660
UNION ALL
SELECT 648
UNION ALL
SELECT 557
UNION ALL
SELECT 650
UNION ALL
SELECT 624
UNION ALL
SELECT 652
UNION ALL
SELECT 636
UNION ALL
SELECT 546
UNION ALL
SELECT 662
UNION ALL
SELECT 634
UNION ALL
SELECT 629
UNION ALL
SELECT 657
UNION ALL
SELECT 638
UNION ALL
SELECT 658
UNION ALL
SELECT 659
UNION ALL
SELECT 661
UNION ALL
SELECT 625) x ON x.brand_id = t.brand_id
You could consider populating a temp table, to use in place of the derived one you see in my answer.
[1] 642, 630, 513, 637, 632, 556, 548, 628, 651, 660, 648, 557, 650, 624, 652, 636, 546, 662, 634, 629, 657, 638, 658, 659, 661, 625
Since you have hardcoded it, i assume these are the numbers you want to exclude all the time.
Then, why not create a table that only contains these ids, and another table that doesn't contain these ids. And, your insert determines which table to be inserted into based on the 'brand_id'.
[2] 662, 661, 660, 659, 658, 657, 652, 651, 650, 648, 642, 638, 637, 636, 634, 632, 630, 629, 628, 625, 624, 557, 556, 548, 546, 513
A sorted list of your brand_ids is shown above. It looks like you can lower the equality calls a few more by providing a range condition instead. (ie., >=657 && <=662, >=650 &&<=652, etc..)
The long pole in this query is the "DISTINCT" clause.
First, I'm not sure why you said "this is a dynamically generated query, so none of the temporary table options that some of you have offered, while an excellent idea, were available to me." It's possible to use temporary tables with dynamically generated queries ... ? Perhaps you meant something else.
Can you at least build a supporting table for this? Something like:
CREATE TABLE product_applications_brand_id_attribute1 (
PRIMARY KEY (attribute1)
) IGNORE AS SELECT attribute1
FROM product_applications
WHERE brand_id NOT IN (
642, 630, 513, 637, 632, 556, 548, 628, 651, 660,
648, 557, 650, 624, 652, 636, 546, 662, 634, 629,
657, 638, 658, 659, 661, 625)
Then, you would query:
SELECT attribute1
FROM product_applications_brand_id_attribute1
This isn't an ideal solution because you have to update the new separate table every time there's a change in the base table.
精彩评论