使用IN运算符进行慢查询

Question

I am having a problem with one query. I am sure that the problem is IN operator but I don't know how and with what should I replace it?

Query:

SELECT `a`.`title`, `a`.`askprice`, `a`.`picture`, `a`.`description`, `a`.`userid`, 
`a`.`id`
FROM (`mm_ads` AS a)
LEFT JOIN `mm_ads_fields_values` AS afv ON `a`.`id` = `afv`.`aid`
WHERE `a`.`status` = 1
AND `a`.`category` = '230'
AND `a`.`id` IN (2194,2200,2205,2215,2233,2238,2240,0,2243,2255,2257,2260,2263,2264,2266,2269,2272,2274,2276,2277,2279,2282,2284,2286,2288,2293,2297,2300,2303,2306,2310,2311,2318,2322,2324,2326,2330,2335,2340,2347,2351,2357,2364,2370,2375,2387,2391,2399,2404,2408,2413,2571,2574,2576,2578,2580,2582,2584,2586,2588,2590,2592,2597,2599,2601,2605,2606,2612,2615,2618,2621,2626,2628,2632,2637,2639,2641,2643,2646,2647,2653,2662,2664,2667,2670,2673,2677,2680,2687,2689,2691,2693,2696,2698,2701,2703,2705,2708,2710,2711,3666,3674,3693,3697,3704,3711,3718,3727,3732,3736,3761,3764,3771,3776,3779,3786,3802,3807,3813,3818,3844,3847,3853,3866,3870,3873,3879,3883,4283,4287,4292,4300,4305,4313,4332,4339,4348,4354,4361,4367,4371,4378,4391,4412,4424,4441,4445,4451,4458,4466,4473,4479,4494,4508,4515,4523,4530,4537,4541,4542,4575,4584,4602,4621,4629,4638,4647,4652,4659,4665,4670,4677,4684,4690,4693,4702,4712,4720,4726,4732,4740,4747,4755,4763,4772,4776,4779,4784,4788,4790,4793,4797,4802,4807,4811,4818,4824,4831,4839,4844,4848,4851,4855,4867,4873,4877,4887,4894,4897,4901,4908,4912,4916,4926,4932,4939,4944,4949,4956,4959,4968,4977,4981,5001,5008,5012,5015,5021,5027,5034,5038,5045,5048,5053,5058,5063,5066,5070,5071,5077,5080,5085,5089,5096,5104,5108,5113,5114,5120,5127,5131,5135,5141,5150,5155,5159,5170,5174,5179,5187,5196,5205,5209,5215,5236,5254,5273,5281,5290,5309,5319,5323,5327,5350,5357,5363,5364,5372,5378,5409,5425,5434,5442,5461,5468,5477,5489,5497,5501,5515,5522,5529,5536,5541,5548,5558,5569,5585,5591,5597,5611,5619,5626,5635,5644,5653,5661,5667,5680,5688,5695,5702,5713,5725,5730,5734,5766,5781,5830,5882,5896,5928,5936,5943,5952,5958,5973,5998,6007,6018,6026,6083,6097,6103,6109,6114,6119,6124,6130,6136,6144,6152,6158,6172,6181,6192,6202,6211,6218,6223,6226,6230,6234,6239,6246,6256,6259,6266,6269,6273,6277,6282,6286,6291,6297,6301,6305,6309,6312,6315,6321,6326,6333,6338,6347,6352,6358,6365,6367,6372,6383,6389,6396,6401,6408,6413,6420,6424,6433,6471,6477,6487,6493,6498,6502,6506,6509,6511,6514,6519,6532,6539,6546,6551,6557,6562,6568,6577,6611,6618,6622,6625,6630,6635,6639,6648,6650,6653,6657,6661,6664,6671,6672,6679,6683,6687,6692,6695,6700,6704,6709,6712,6713,6716,6719,6724,6725,6733,6736,6754,6776,6781,6784,6791,6794,6807,6811,6816,6821,6826,6827,6867,6873,6879,6883,6893,6900,6908,6913,6919,6925,6931,6935,6944,6950,6955,6959,6964,6967,6970,6986,6990,6995,6998,7001,7003,7007,7012,7015,7019,7023,7025,7030,7033,7037,7041,7046,7052,7053,7057,7060,7063,7065,7068,7081,7085,7090,7091,7094,7096,7101,7107,7113,7116,7119,7122,7130,7138,7153,7154,7158,7161,7164,7167,7177,7179,7190,7198,7203,7207,7211,7215,7225,7229,7235,7240,7242,7248,7253,7256,7258,7259,7263,7269,7275,7277,7282,7285,7290,7294,7298,7302,7306,7310,7316,7318,7321,7326,7331,7336,7339,7345,7348,7355,7359,7364,7369,7374,7379,7383,7386,7391,7395,7399,7403,7410,7415,7418,7423,7427,7432,7434,7438,7457,7458,7460,7463,7465,7468,7471,7474,7476,7479,7481,7483,7485,7487,7490,7492,7494,7498,7501,7504,7507,7511,7514,7517,7520,7525,7530,7534,7540,7543,7546,7548,7551,7591,7596,7599,7606,7609,7615,7622,7629,7639,7643,7648,7650,7658,7662,7667,7670,7677,7681,7686,7691,7693,7700,7702,7707,7712,7716,7725,7740,7746,7752,7759,7760,7763,7766,7772,7776,7985,7993,8000,8007,8028,8035,8042,8050,8057,8067,8074,8084,8091,8097,8103,8109,8111,8129,8233,8237,8241,8245,8249,8252,8260,8264,8292,8300,8309,8397,8420,8426,8430,8434,8437,8444,8448,8454,8462,8470,8477,8483,8488,8492,8499,8509,8515,8520,8526,8531,8538,8543,8548,8552,8557,8561,8565,8569,8573,8576,8581,8586,8594,8599,8608,8612,8615,8620,8624,8629,8632,8638,8641,8646,8650,8654,8655,8660,8665,8668,8673,8675,8681,8686,8691,8695,8700,8704,8708,8713,8718,8722,8727,8742,8747,8780,8783,8786,8789,8793,8797,8802,8807,8810,8813,8816,8819,8821,8825,8828,8831,8834,8839,8844,8847,8852,8853,8855,8858,8861,8864,8868,8872,8874,8878,8880,8884,8888,8891,8897,8902,8907,8916,8917,8921,8925,8929,8934,8939,8946,8959,8960,8966,8972,8979,8982,8987,8992,8994,9008,9011,9014,9018,9023,9027,9031,9035,9040,9043,9046,9053,9057,9061,9066,9071,9075,9078,9081,9086,9090,9095,9102,9105,9112,9117,9121,9129,9132,9137,9140,9142,9145,9151,9156,9171,9176,9192,9196,9202,9207,9213,9219,9222,9225,9229,9235,9242,9250,9263,9269,9273,9278,9284,9295,9303,9304,9305,9311,9319,9322,9328,9332,9335,9339,9346,9350,9355,9361,9366,9370,9375,9379,9382,9385,9392,9397,9400,9404,9410,9413,9418,9421,9424,9437,9441,9446,9451,9457,9466,9477,9481,9492,9495,9511,9516,9519,9523,9526,9532,9543,9684,9690,9387,8911,8228,8116,7756,6887,6829,5966,5335,5315,5300,4400,3840,12881,12893,12898,1180,1499,2188,2251,2256,2295,9462,9529,9750,9922,10087,10107,11089,11392,9605,9607,9612,9615,9619,9627,9633,9638,9640,9642,9669,9677,9681,9694,9697,9703,9707,9709,9711,9714,9716,9720,9726,9730,9733,9737,9740,9745,9752,9757,9767,9776,9778,9779,9784,9787,9789,9792,9796,9799,9803,9806,9811,9818,9820,9826,9827,9832,9836,9845,9847,9850,9852,9855,9858,9861,9862,9864,9868,9871,9875,9882,9886,9889,9892,9897,9899,9903,9907,9913,9916,9920,9926,9928,9930,9932,9936,9940,9943,9946,9950,9954,9957,9962,9964,9966,9973,9976,9977,9980,9981,9985,9986,9987,9989,10019,10022,10025,10027,10028,10055,10062,10189,10195,10197,10212,10220,10221,10224,10226,10229,10231,10232,10235,10428,10431,10434,10438,10440,10442,10443,10445,10448,10449,10455,10459,10466,10469,10471,10473,10474,10475,10476,10478,10481,10484,10486,10489,10493,10494,10496,10497,10499,10501,10502,10503,10508,10511,10514,10516,10518,10521,10524,10528,10529,10532,10160,10158,10156,10153,10149,10146,10138,10133,10132,10129,10126,10123,10119,10117,10116,10112,10111,10109,10101,10099,10097,10093,10090,10086,10084,10082,10078,10074,10070,10066,10045,10052,10041,10037,10036,10033,18596)
AND `a`.`userid` = '136'
GROUP BY `a`.`id`
ORDER BY `id` DESC, `id` DESC
LIMIT 15;

How can I improve the speed of that query?

Let me explain my tables:

mm_ads: Table where ads are stored in mm_ads_fields_values: Table where options for ads are stored in.

Example: Car is ad and having one row in table mm_ads. Car has following options: brand, model, color,... So Car ad has more than one row in table mm_ads_fields_values.

So if I search only cars with "black" color, the query will get first all IDs that have color option set to "black". Then I put these IDs to query.

Update: Columns in table mm_ads_fields_values: id, aid, db_name, value

Sorry for my bad English..

Answer 1

Remove the LEFT JOIN and GROUP BY parts:

SELECT  `a`.`title`, `a`.`askprice`, `a`.`picture`, `a`.`description`, `a`.`userid`, `a`.`id`
FROM    `mm_ads` AS a
WHERE   `a`.`status` = 1
        AND `a`.`category` = '230'
        AND `a`.`id` IN (…)
        AND `a`.`userid` = '136'
ORDER BY
        `id` DESC
LIMIT 15

Make sure that id is defined as a PRIMARY KEY.

I'm assuming the query is built by an ORM so recheck your model definition, the LEFT JOIN and GROUP BY are redundant here.

Update:

If you are searching for black cars, you can do it in a single query like this:

SELECT  a.*
FROM    mm_ads_fields_values afv
JOIN    mm_ads a
ON      a.id = afv.aid
WHERE   afv.field = 'color'
        AND afv.value = 'black'
ORDER BY
        afv.aid DESC
LIMIT 15

Create a composite index on mm_ads_fields_values (field, value, aid).

Update 2:

To find black BMW:

SELECT  a.*
FROM    (
        SELECT  aid
        FROM    mm_ads_field_values afv
        WHERE   (field, value) = ('color', 'black')
                OR (field, value) = ('model', 'BMW')
        GROUP BY
                aid
        HAVING  COUNT(*) = 2
        ORDER BY
                aid DESC
        LIMIT 15
        ) af
JOIN    mm_ads a
ON      a.id = af.aid
ORDER BY
        aid DESC