Solved

Why is this MySQL SQL statement not using an available index (and therefore slow)?

Posted on 2015-01-23
1
178 Views
Last Modified: 2015-02-25
The following SQL statement has indexes available on both sides of the join that it showing as using a temporary table and filesort instead of those indexes.

Could someone tell me why and what the solution is?

Second, if it is just a matter of adding an index in the right place, is there a tool that can say, "add an index here to make this query better" or some other such recommendations?

What I cannot do (unfortunately) with this particular query is change it around because it is being auto-generated by a CMS.

Below:
a) Indexes of table ctf
b) Indexes of table node
c) The explain output
d) The query

--------------------
Indexes of table ctf
--------------------
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Edit Drop Drop PRIMARY BTREE Yes No vid 3859 A No
Edit Edit Drop Drop nid BTREE No No nid 3859 A No
Edit Edit Drop Drop field_user_distributor_nid BTREE No No field_user_distributor_nid 1929 A Yes
Edit Edit Drop Drop field_user_default_company_nid BTREE No No field_user_default_company_nid 20 A Yes

---------------------
Indexes of table node
---------------------
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Edit Drop Drop PRIMARY BTREE Yes No nid 202797 A No
Edit Edit Drop Drop vid BTREE Yes No vid 202797 A No
Edit Edit Drop Drop node_changed BTREE No No changed 101398 A No
Edit Edit Drop Drop node_created BTREE No No created 202797 A No
Edit Edit Drop Drop node_moderate BTREE No No moderate 1 A No
Edit Edit Drop Drop node_promote_status BTREE No No promote 1 A No
status 3 A No
Edit Edit Drop Drop node_status_type BTREE No No status 1 A No
type 12 A No
nid 202797 A No
Edit Edit Drop Drop node_title_type BTREE No No title 67599 A No
type (4) 67599 A No
Edit Edit Drop Drop node_type BTREE No No type (4) 11 A No
Edit Edit Drop Drop uid BTREE No No uid 3826 A No
Edit Edit Drop Drop tnid BTREE No No tnid 1 A No
Edit Edit Drop Drop translate BTREE No No translate 1 A No

-----------------------
EXPLAIN EXTENDED OUTPUT
-----------------------
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE ctf ALL nid NULL NULL NULL 3859 100.00 Using temporary; Using filesort
1 SIMPLE n eq_ref PRIMARY,vid,node_type,uid PRIMARY 4 hschein_dev.ctf.nid 1 100.00 Using where
1 SIMPLE cfccr ref PRIMARY PRIMARY 4 hschein_dev.n.vid 1 100.00 Using where
1 SIMPLE u eq_ref PRIMARY,user_force_password PRIMARY 4 hschein_dev.n.uid 1 100.00 Using where; Using index
1 SIMPLE cfuc ref PRIMARY,field_user_company_nid,field_user_company_... PRIMARY 4 hschein_dev.n.vid 1 100.00 Using where; Distinct
1 SIMPLE cfug ref PRIMARY,field_user_group_value PRIMARY 4 hschein_dev.n.vid 1 100.00 Using where; Distinct

-----------------
THE OFFENDING SQL
-----------------
EXPLAIN EXTENDED
SELECT DISTINCT (u.uid),
ctf.field_user_first_name_value AS `first`,
ctf.field_user_last_name_value AS `last`
FROM node n INNER JOIN users u ON n.uid = u.uid
INNER JOIN content_type_profile ctf ON n.nid = ctf.nid
INNER JOIN content_field_client_company_related cfccr ON n.vid = cfccr.vid
INNER JOIN content_field_user_group cfug ON n.vid = cfug.vid
INNER JOIN content_field_user_company cfuc ON n.vid = cfuc.vid
WHERE n.TYPE = 'profile'
AND cfccr.field_client_company_related_value = 'Yes'
AND cfug.field_user_group_value IN
(10445, 10165, 9091, 9093, 9094, 9092, 9090, 9097, 9694, 9706, 10203, 10393,
10394, 10153, 9827, 9842, 10089, 9934, 9817, 10098, 10010, 9327, 9354, 9376,
9513, 9648, 9696, 10169, 10381, 10392, 10370, 9755, 9756, 9331, 9451, 9494, 9573
, 9681, 10205, 10383, 10365, 10368, 9795, 9750, 9876, 9892, 9391, 9398, 9397,
9844, 9396, 9479, 9547, 9490, 9554, 9716, 9724, 10171, 10327, 10439, 10438, 9002
, 9105, 9824, 9823, 9834, 9843, 9862, 9915, 9970, 9949, 9950, 9952, 9744, 9984,
9861, 9993, 10040, 10052, 10059, 10065, 10064, 10071, 9979, 9306, 10031, 9340,
9345, 9353, 9337, 9349, 10043, 9344, 9364, 9362, 9374, 9375, 9379, 10048, 9390,
9392, 9406, 10074, 9438, 9441, 9967, 9464, 9466, 9485, 9495, 9831, 9499, 9502,
9515, 9882, 9526, 9569, 9570, 9591, 9607, 9611, 9613, 9614, 9618, 8933, 9631,
9874, 9553, 9637, 9639, 9642, 9644, 9651, 9656, 9655, 9659, 9661, 9663, 9669,
9674, 9671, 9621, 9683, 9693, 9688, 9703, 9705, 9704, 9715, 9431, 9734, 9818,
10112, 9763, 9948, 9769, 9955, 9910, 9302, 9945, 9960, 9888, 9799, 10005, 9837,
9808, 9847, 9887, 9807, 9803, 9813, 9804, 9894, 9856, 9775, 10001, 9838, 9936,
10174, 10367, 10190, 10322, 10200, 10298, 10210, 10418, 10344, 10345, 10351,
10295, 10434, 9082, 10234, 10230, 10244, 10241, 10233, 10232, 10238, 10231,
10236, 10235, 10237, 10240, 10239, 10243, 10433, 10420, 10430, 10431, 10432,
10421, 9890, 9995, 9317, 9821, 9851, 9736, 9992, 9363, 9449, 9972, 9535, 9571,
9583, 9653, 10179, 10287, 10402, 9786, 9864, 10127, 9304, 9764, 10111, 10013,
10020, 9322, 9484, 9518, 9728, 9592, 10120, 9645, 9649, 10184, 10315, 8925, 9027
, 8909, 8908, 9244, 9086, 8924, 9128, 8926, 10306, 10443, 10442, 10422, 10223,
8931, 10006, 10224, 10409, 9191, 9196, 9187, 9001, 10356, 10217, 8998, 9295,
9220, 9163, 9010, 9096, 9088, 9171, 9182, 9069, 9122, 9239, 9213, 9036, 9194,
9178, 8990, 9190, 9177, 9183, 9255, 9015, 9199, 9063, 9006, 9225, 9198, 9032,
9162, 9060, 9175, 9206, 9076, 9078, 9099, 8950, 8943, 9203, 9065, 9207, 8948,
9021, 10222, 8947, 8946, 9204, 9103, 9161, 9197, 8992, 9170, 9039, 9173, 9195,
9061, 9167, 9180, 9000, 9176, 9108, 9188, 9059, 9110, 9241, 9240, 9087, 9164,
9201, 9200, 9205, 9055, 9181, 9238, 9193, 9028, 9262, 9172, 9165, 9174, 9260,
9263, 9083, 9179, 9231, 9184, 9192, 9186, 9185, 9169, 8995, 9189, 9057, 9202,
9166, 8958, 8967, 9168, 9035, 8988, 8940, 10346, 10388, 9020, 10277, 9252, 8941,
10221, 8951, 9251, 9247, 9253, 9250, 10220, 9248, 8934, 9341, 9338, 9356, 9380,
9386, 9394, 9393, 9400, 10072, 10137, 9423, 9372, 9440, 9867, 9533, 9534, 10144,
9313, 9598, 9609, 9597, 9620, 9629, 9635, 9668, 9672, 9673, 9699, 9700, 9721,
10117, 9954, 9980, 9869, 9729, 9982, 10018, 10147, 9870, 9781, 9759, 9860, 10041
, 9850, 10284, 9735, 10328, 10289, 10336, 10330, 8953, 9071, 9148, 9149, 8993,
9072, 9117, 9150, 9077, 9081, 9147, 9101, 9107, 9045, 9146, 9018, 9153, 9151,
9133, 9056, 9154, 9258, 9266, 9135, 9156, 8959, 9159, 9208, 9211, 9222, 9221,
8968, 8964, 9013, 9113, 8975, 8989, 9031, 10110, 10125, 10146, 10080, 10094,
9780, 9909, 9959, 10055, 9346, 9358, 9343, 9381, 9964, 9410, 9422, 9511, 9531,
9507, 9625, 9602, 10333, 10369, 10178, 10177, 10185, 10194, 10196, 10180, 10197,
10199, 10170, 10188, 8930, 10173, 10209, 10187, 10193, 10172, 10191, 10176,
10183, 10192, 10401, 10395, 10399, 9491, 9492, 9501, 9667, 9725, 10378, 10403,
10092, 10141, 9822, 9798, 9436, 9519, 9606, 9670, 9678, 10307, 9991, 9784, 10025
, 9924, 9757, 9976, 9361, 9429, 9446, 9325, 9471, 9617, 10340, 9832, 9779, 10136
, 9940, 9819, 9863, 9997, 10087, 9996, 9884, 10116, 9962, 10019, 9785, 10108,
10073, 9368, 9428, 9463, 9473, 9561, 9593, 9527, 9928, 9603, 9630, 8935, 9709,
10300, 10119, 9885, 9906, 10063, 9883, 9833, 9916, 10002, 9332, 9365, 9383, 9453
, 9468, 9579, 9594, 9646, 9654, 9675, 10361, 10323, 10411, 10412, 10404, 10408,
10121, 10118, 9990, 10107, 9855, 9746, 9416, 9540, 9557, 9558, 9576, 9586, 9600,
9574, 9664, 10347, 10391, 9875, 9971, 9820, 10145, 9836, 9918, 9581, 9944, 9914,
9330, 9411, 9443, 9562, 9666, 9427, 9726, 9727, 10348, 10350, 8916, 8913, 8911,
8910, 8915, 10245, 8912, 9891, 10129, 9933, 10149, 9917, 9981, 10050, 10126,
10357, 10337, 8927, 8923, 8917, 8922, 8920, 8921, 8919, 8918, 9050, 9134, 8973,
9155, 8929, 9111, 9139, 9144, 9136, 9245, 9140, 10229, 9142, 9218, 9130, 9143,
9141, 9121, 9009, 9132, 9037, 9145, 9073, 9131, 9129, 9052, 9229, 9137, 9070,
9053, 9138, 9058, 9100, 8944, 9014, 9157, 9158, 8965, 9120, 8952, 9223, 9219,
9212, 9214, 9116, 9089, 9044, 9098, 9017, 8972, 9230, 9233, 9016, 9048, 9085,
9034, 9062, 9264, 10167, 9074, 10213, 9160, 9124, 8942, 8945, 9118, 9123, 9051,
8961, 8955, 10286, 8957, 8987, 8960, 9064, 9084, 9042, 8963, 8971, 9115, 9068,
9080, 8980, 8982, 8984, 9029, 9215, 10021, 10076, 10011, 9911, 10095, 10014,
10155, 10097, 9789, 9771, 9805, 9761, 10068, 10044, 10003, 10103, 10132, 9792,
9318, 9811, 9828, 10017, 10053, 9319, 9320, 9324, 9360, 10008, 9452, 9504, 9509,
9508, 9489, 9544, 9545, 8937, 9647, 9712, 10363, 10398, 10414, 10303, 9793, 9829
, 9412, 9845, 10039, 10131, 9919, 10023, 10070, 9401, 9402, 9405, 9516, 9522,
9999, 10242, 10354, 9310, 10164, 10156, 9998, 9315, 9878, 9866, 9932, 9777, 9881
, 10154, 10090, 9920, 10034, 9753, 9768, 9925, 9303, 10135, 10124, 10150, 9797,
10139, 10282, 9407, 9408, 9424, 9460, 9462, 9476, 9538, 9551, 9572, 9590, 9623,
10279, 10288, 10313, 9420, 9774, 10046, 9895, 9751, 9900, 10030, 9333, 10283,
10280, 9486, 9498, 9806, 9610, 9622, 9641, 9697, 9707, 10416, 10349, 9853, 10049
, 10082, 9873, 9366, 9367, 9388, 9437, 9529, 9552, 9605, 9713, 10360, 10091,
9904, 10077, 10281, 9480, 10302, 10332, 10389, 10377, 10314, 10372, 9049, 9112,
9276, 9293, 9125, 9270, 9259, 9292, 9271, 9227, 9288, 8969, 9217, 9256, 9127,
9281, 9268, 9277, 9265, 9234, 9040, 9237, 9291, 9287, 8962, 9228, 8970, 8956,
9119, 9275, 9280, 9283, 9284, 9012, 9011, 9043, 9235, 8978, 8954, 9269, 9282,
9261, 9272, 9226, 9243, 9278, 8981, 9273, 9294, 9296, 9279, 9236, 9216, 9257,
9075, 8949, 9209, 9286, 9289, 9210, 9267, 8997, 8991, 9004, 9005, 10228, 9095,
9254, 9285, 9249, 9290, 8976, 9232, 9126, 8979, 9038, 9152, 8939, 8977, 9054,
9224, 8983, 9033, 10364, 10212, 10004, 10081, 10075, 9760, 9740, 10028, 10101,
9889, 9931, 9741, 9877, 9922, 9929, 10115, 9359, 9433, 9448, 9457, 9469, 10069,
9493, 9497, 9500, 9548, 9612, 9682, 9698, 10331, 10355, 10151, 10296, 10215,
10320, 10312, 10326, 9986, 9839, 9481, 9566, 9568, 9624, 10339, 9782, 9772, 9865
, 10086, 9815, 9841, 9942, 10007, 9733, 9974, 9852, 9879, 9935, 9812, 9908,
10012, 9977, 9347, 9399, 9432, 9467, 8932, 9520, 9521, 9546, 9550, 9582, 9577,
9626, 8936, 9578, 9676, 9643, 9720, 10342, 10142, 9921, 9994, 9328, 9523, 9560,
9615, 10321, 10122, 10093, 9927, 9447, 9854, 9708, 10385, 9430, 10042, 9737,
9458, 9461, 9478, 9477, 9409, 9506, 9530, 9563, 9595, 9596, 8938, 9650, 9660,
9599, 9532, 9901, 10078, 9505, 10032, 10143, 9963, 9859, 9858, 9880, 9871, 10106
, 9957, 9975, 9814, 9825, 9896, 9791, 9830, 9983, 10015, 10016, 9314, 9326, 9334
, 9342, 9355, 9370, 10036, 9377, 9382, 9389, 9403, 9415, 10359, 10373, 10297,
10291, 10334, 9722, 9913, 9912, 9978, 9773, 10113, 9308, 10026, 9985, 9848, 9930
, 9309, 9794, 10152, 9800, 9783, 10038, 9738, 10051, 10084, 9425, 9435, 9488,
10022, 9536, 9555, 9565, 9588, 9633, 9657, 9679, 9710, 10329, 10294, 10384,
10380, 9316, 9801, 9809, 9810, 9426, 9434, 9442, 9459, 9470, 9487, 9512, 9601,
9439, 10325, 10128, 9307, 9754, 9947, 9939, 9988, 9943, 9348, 9352, 9953, 9951,
9619, 9528, 10376, 9636, 9638, 9684, 9686, 9695, 9711, 9718, 9719, 9886, 9946,
9907, 9776, 9739, 9816, 9857, 9305, 10000, 9765, 9973, 9770, 9778, 9752, 9868,
9926, 9937, 9961, 9987, 10024, 10037, 9336, 9339, 9371, 9628, 9384, 9404, 9395,
9419, 9445, 9549, 9564, 9584, 9604, 10208, 10310, 10353, 10309, 10379, 8966,
8974, 9007, 10436, 9041, 10428, 10410, 9246, 9242, 9274, 10159, 10160, 10158,
10429, 10427, 9758, 10083, 9966, 10045, 9323, 9826, 9450, 9537, 9465, 10305,
10140, 9872, 9616, 10308, 10218, 10352, 10362, 10375, 10219, 10157, 10161, 10162
, 10163, 10166, 10415, 10186, 10182, 10338, 10316, 10341, 10301, 10413, 10343,
8914, 10425, 9483, 9539, 9585, 9503, 9608, 9543, 9444, 9687, 9723, 10285, 9788,
9311, 10029, 9989, 9902, 10066, 9762, 9790, 10134, 9329, 10387, 10366, 10386,
10100, 9796, 9956, 9965, 10058, 10061, 10062, 9787, 9849, 9351, 9840, 9378, 9385
, 9414, 9456, 9472, 9482, 9496, 9517, 9524, 9525, 9556, 9559, 10047, 9567, 9510,
9580, 9575, 9587, 9301, 9627, 9634, 9652, 9658, 9662, 9689, 9691, 9690, 9692,
9717, 10099, 10104, 9732, 9941, 9958, 9766, 9938, 10054, 10067, 9802, 9968,
10079, 10035, 10102, 9893, 9897, 10374, 10335, 10417, 10311, 10435, 10304, 9749,
9373, 10096, 9312, 9730, 9969, 10088, 9350, 9417, 9321, 9640, 9677, 10382, 9748,
9743, 10109, 9905, 9846, 9923, 10105, 10123, 9335, 9413, 9747, 10278, 10056,
9474, 9475, 9767, 9903, 9541, 9680, 9685, 9714, 9454, 10299, 10317, 10318, 8985,
8986, 8994, 10214, 9008, 9023, 9024, 9025, 9047, 9066, 9102, 9109, 9026, 9104,
9106, 8996, 9022, 9019, 9067, 9079, 9114, 9030, 9046, 9003, 8999, 10133, 10148,
9835, 9899, 9898, 10009, 9357, 10057, 10114, 9387, 9421, 9455, 9514, 9542, 9632,
9701, 9702, 10292, 9731, 10060, 9742, 10027, 9745, 10138, 10130, 10033, 9369,
9589, 9418, 9665, 10319, 10396, 10400, 10407, 10290, 10440, 10085, 10390, 10371,
10293, 10441, 10437, 10444, 10227, 9298, 8928, 10216, 9299, 9297, 9300, 10168,
10225, 10226, 10198)
AND cfuc.field_user_company_nid IN
(20595, 20593, 20719, 20718, 489350, 139760, 20720, 20584, 20721, 20587, 20586,
20585, 20702, 161721,161720, 20588, 20722, 20590, 20589, 20724, 20725, 20727)
ORDER BY ctf.field_user_first_name_value ASC

Open in new window

0
Comment
Question by:RegProctor
1 Comment
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 40567266
once your in list grows above a certain size , optimisers usually decide that it is  quicker to do a full table scan rather bothering with indexes...

that could be a factor here....

why is such a large in list being generated?



why is such a large i list being generated ?
it feels like you have some sort of categorisation table missing from the system, which may enable
a more index approach to be taken by the system.... (it can be add another table to the system and reap some benefits...?)

.... the tool you are looking for is basically packaged as an experienced performance tuning DBA.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question