[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2015-01-23
1
Medium Priority
?
201 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 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