MySQL Query - Alternation of WHERE IN
Posted
by Sadiqur Rahman
on Stack Overflow
See other posts from Stack Overflow
or by Sadiqur Rahman
Published on 2010-05-16T09:37:41Z
Indexed on
2010/05/16
9:40 UTC
Read the original article
Hit count: 284
I have a mySQL query which takes 3-4 minutes to be executed. It is a large database. This query uses WHERE IN to find the rows.. So, is there any alternate query/clause/statement for my this query?
SELECT r.reg_id, r.first_name, r.last_name, r.email, r.country, e.headline,
e.industry, pp.photo FROM basic_registration r LEFT JOIN exp_ind_reg e ON
e.reg_id=r.reg_id LEFT JOIN profile_photo pp ON pp.reg_id=r.reg_id WHERE r.reg_id IN
(23,228,497,593,761,1204,1491,1894,1895,2128,7,11,20,22,25,26,27,29,31,
32,33,34,37,41,45,47,50,52,53,54,55,62,63,69,75,79,80,82,85,87,88,89,93,96,99,
102,104,106,110,116,117,124,139,143,146,150,157,159,161,162,170,175,176,177,
181,183,197,210,213,215,217,220,226,227,233,240,250,252,255,262,263,268,274,280,
283,285,290,300,312,313,317,324,332,341,347,351,357,368,369,372,373,377,
381,383,398,408,414,416,418,419,422,432,441,446,450,451,453,463,466,469,473,486,511,
522,525,527,529,534,538,541,543,546,564,566,569,577,579,581,585,586,595,598,599,600,
606,611,613,614,621,640,649,654,656,660,667,668,674,682,686,689,693,699,705,720,
734,742,748,753,763,774,775,780,782,784,792,795,804,839,841,862,871,890,929,
930,943,951,965,994,1004,1017,1026,1034,1050,1051,1053,1054,1067,1082,1087,1109,
1119,1121,1124,1136,1147,1187,1197,1214,1224,1226,1230,1241,1255,1318,1323,1358,1361,
1383,1404,1415,1429,1440,1443,1452,1458,1473,1478,1484,1490,1496,1505,1508,1521,
1534,1544,1556,1575,1628,1640,1644,1660,1688,1725,1791,1802,1815,1819,1849,1850,1891,
1896,1897,1911,1917,1923,1924,1926,1927,1930,1956,1959,1961,1967,1983,2006,2016,
2028,2053,2059,2088,2089,2100,2136,2145,2164,2183,2190,2219,2243,2291,2301,2321,
2343,2345,2423,2438,2465,2478,2501,2507,2508,2551,2563,2572,2629,2636,2642,2650,
2670,2693,2695,2724,2732,2801,2803,2839,2847,2867,2899,3024,3061,3068,3071,3093,
3123,3126,3188,3240,3273,3307,3308,3332,3484,3493,3522,3552,3596,3632,3705,3769,
3845,3869,3966,3969,4046,4066,4074,4077,4108,4113,4140,4198,4213,4218,4266,4295,
4312,4345,4365,4369,4380,4425,4453,4485,4486,4488,4493,4494,4495,4500,4513,4515,
4517,4520,4533,4540,4542,4544,4548,4550,4551,4554,4555,4557,4566,4567,4568,
4570,4572,4575,4586,4587,4590,4593,4594,4595,4598,4599,4608,4640,4642,4647,4650,
4661,4664,4679,4681,4685,4686,4698,4707,4708,4709,4711,4712,4714,4715,4717,4719,
4720,4721,4722,4724,4725,4728,4729,4732,4734,4735,4736,4737,4739,4742,4744,4745,
4750,4752,4754,4755,4757,4759,4760,4761,4763,4764,4766,4768,4770,4772,4774,4776,
4777,4789,4790,4791,4793,4795,4796,4797,4799,4803,4804,4805,4806,4808,4809,4811,
4814,4815,4817,4818,4821,4825,4826,4828,4830,4831,4833,4835,4836,4837,4843,4844,
4847,4848,4852,4853,4854,4861,4865,4866,4871,4874,4875,4876,4879,4880,4886,4889,
4890,4891,4892,4893,4894,4896,4899,4900,4904,4908,4914,4915,4916,4917,4918,4922,
4925,4929,4930,4931,4932,4934,4935,4940,4943,4944,4945,4947,4948,4949,4952,4953,
4956,4961,4963,4964,4965,4973,4974,4976,4978,4980,4985,4988,4989,4990,4993,4996,
5001,5009,5014,5016,5017,5018,5019,5021,5023,5024,5025,5028,5032,5033,5041,5042,
5048,5055,5056,5058,5059,5062,5065,5066,5072,5073,5075,5078,5079,5083,5084,5085,
5086,5087,5088,5089,5090,5091,5092,5093,5094,5096,5103,5112,5115,5116,5117,5123,
5125,5126,5127,5128,5130,5131,5132,5133,5134,5137,5138,5139,5140,5141,5146,5148,
5150,5155,5156,5158,5161,5162,5163,5164,5166,5168,5172,5174,5176,5178,5179,5180,
5181,5183,5186,5191,5194,5199,5200,5201,5202,5206,5214,5215,5217,5218,5222,5225,
5226,5227,5235,5236,5237,5243,5245,5246,5248,5251,5252,5254,5255,5256,5257,
5259,5261,5262,5267,5270,5271,5275,5279,5281,5283,5284,5286,5288,5289,5292,5293,
5295,5307,5308,5310,5311,5313,5315,5321,5323,5324,5325,5327,5328,5339,5340,5345,
5351,5353,5355,5356,5357,5358,5359,5363,5364,5365,5366,5369,5370,5371,5372,5373,
5376,5377,5378,5379,5381,5382,5383,5384,5385,5386,5387,5388,5389,5390,5393,5395,
5405,5406,5407,5411,5413,5414,5415,5416,5417,5418,5420,5424,5425,5429,5430,5431,
5432,5433,5434,5435,5437,5441,5451,5460,5467,5473,5476,5506,5524,5528,5530,5534,
5535,5536,5550,5551,5552,5553,5554,5556,5557,5559,5564,5565,5567,5568,5574,5575,
5585,5586,5587,5597,5600,5601,5605,5606,5607,5613,5614,5615,5617,5618,5624,5626,
5627,5628,5640,5643,5644,5645,5647,5648,5649,5650,5660,5661,5670,5671,5673,5674,
5675,5681,5683,5685,5689,5690,5691,5692,5693,5694,5695,5696,5697,5702,5703,5704,
5705,5706,5708,5710,5711,5712,5713,5716,5717,5719,5730,5732,5737,5744,5745,5746,
5748,5749,5750,5752,5753,5754,5756,5757,5758,5759,5761,5762,5763,5764,5765,5767,
5769,5770,5776,5780,5782,5783,5784,5787,5788,5789,5790,5791,5792,5793,5794,5799,
5802,5803,5804,5805,5806,5808,5809,5810,5812,5813,5814,5816,5817,5818,5822,5823,5826,
5827,5829,5830,5831,5848,5849,5850,5851,5852,5854,5856,5858,5859,5863,5864,5865,
5866,5867,5873,5884,5885,5893,5898,5899,5904,5907,5908,5910,5911,5915,5916,5918,
5919,5922,5923,5924,5933,5934,5941,5944,5950,5954,5955,5956,5960,5961,5973,5978,5981,
5982,5983,5984,5985,5986,5987,5988,5989,5990,5998,5999,6000,6002,6003,6004,6006,
6007,6010,6093,6175,6177,6217,6236,6325,6327,6347,6398,6403,6447,6582,6586,6609,
6697,6904,6926,6933,7001,7003,7047,7081,7094,7111,7205,7207,7219,7220,7221,7222,
7224,7227,7228,7229,7230,7232,7237,7238,7241,7268,7274,7275,7276,7281,7300,7307,
7309,7315,7330,7333,7334,7339,7343,7348,7354,7360,7374,7377,7378,7390,7429,7434,
7445,7448,7449,7452,7532,7534,7539,7542,7546,7547,7555,7563,7565,7567,7572,7575,
7576,7577,7578,7579,7585,7611,7907,7926,8100,8134,8205,8324,8337,8339,8350,8351,
8362,8410,8568,8572,8618,8619,8651,8665,8666,8667,8668,9010,9068,9098,9100,9106,
9111,9115,9121,9123,9174,9177,9272,9302,9421,9570,9683,9684,9697,9704,9712,9715,9779,
9790,9792,9793,9795,9798,9814,9818,9856,9866,9876,9886,9891,9908,9912,9928,10508,
10825,11103,11729,12289,12377,12643,12656,12657,12668,12876,12926,12958,13291,
13300,13408,13472,13976,14477,14538,14833,15044,15108,15779,16039,16061,16549,
16556,16562,16564,16565,16571,16573,16574,16576,16577,16584,16589,16590,16591,
16592,16598,16604,16606,16607,16610,16620,16645,16648,16650,16654,16655,16661,
16662,16675,16680,16697,16699,16701,16702,16704,16705,16708,16714,16719,16723,
16724,16727,16729,16731,16732,16743,16750,16752,16755,16758,16772,16774,16782,16787,
16793,16794,16795,16797,16798,16802,16813,16814,16815,16824,16825,16829,16831,
16841,16843,16848,16850,16863,16864,16866,16870,16878,16881,16887,16893,16896,16897,
16900,16902,16909,16912,16936,16944,16948,16958,16960,16963,16974,16978,16993,17012,
17016,17020,17053,17061,17096,17120,17124,17125,17129,17135,17137,17140,17141,17142,
17145,17149,17150,17157,17164,17170,17172,17173,17178,17180,17184,17187,17188,
17192,17196,17197,17200,17201,17206,17207,17221,17223,17227,17236,17244,17246,
17273,17285,17289,17291,17297,17300,17305,17310,17311,17321,17326,17331,17335,
17352,17370,17414,17423,17424,17439,17479,17493,17495,17501,17519,17525,17541,
17571,17590,17614,17755,17838,17846,17848,17852,17853,17855,17858,17861,17871,
17876,17877,17891,17896,17899,17900,17905,17908,17910,17911,17916,17917,17938,17939,
17940,17949,17953,17955,17960,17972,17980,17982,17992,18055,18067,18069,18071,18077,
18108,18127,18134,18136,18140,18142,18143,18158,18162,18178,18192,18196,18206,18217,
18221,18242,18245,18249,18263,18271,18273,18275,18277,18278,18286,18291,18295,18300,
18301,18308,18325,18333,18338,18360,18373,18374,18387,18397,18411,18412,18420,18429,
18434,18455,18478,18484,18534,18779,18790,18804,18821,18851,18964,18965,18977,18990,
18991,19000,19006,19276,19291,19374,19395,19416,19432,19627,19917,19927,19971,19974,
19989,20007,2254,2549,2652,3077,3615,4483,4484,4611,4700,5714,5772,6252,6536,7051,
7102,7107,7591,8167,8286,8935,9937,11089,12344,15830,16343,16644,17359,
17994,18774)
AND r.activation=1 ORDER BY r.first_name ASC LIMIT 0, 10;
© Stack Overflow or respective owner