Solved

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

Posted on 2015-01-23
1
170 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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now