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

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

LVL 1
RegProctorAsked:
Who is Participating?
 
LowfatspreadCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.