Solved

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

Posted on 2015-01-23
1
184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

717 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