StackOverflowNewbie
Reputation: 40653
MySQL: super slow SQL
I have a query that is taking almost 8 seconds to execute. I did an EXPLAIN on the query, but I don't know how to interpret the results. Can someone please help me troubleshoot this problem? Here are the results of the EXPLAIN:
===============================================================================================================================================================================================
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
===============================================================================================================================================================================================
| 1 | PRIMARY | <derived2> | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 669 | Using filesort |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | DERIVED | Workflow | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | DERIVED | DataSource | ref | PRIMARY,WorkflowId | WorkflowId | 4 | | 1546 | Using where |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | DERIVED | ReadyLog | ALL | DataSourceId | (NULL) | (NULL) | (NULL) | 9463 | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | DERIVED | DataSourceActivityLog | eq_ref | PRIMARY,DataSourceId | PRIMARY | 4 | func | 1 | Using where |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | DERIVED | User | eq_ref | PRIMARY | PRIMARY | 4 | my_db.DataSourceActivityLog.UserId | 1 | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | DEPENDENT SUBQUERY | DataSourceActivityLog | ref | DataSourceId | DataSourceId | 4 | my_db.DataSource.Id | 1135 | Using where; Using filesort |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If there is additional information you need from me, please let me know. Thanks.
UPDATE 1: the ugly query:
SELECT WrappedData.*
FROM (SELECT DataSource.Id,
DataSourceActivityLog.Description,
DataSourceActivityLog.UserId,
DataSource.Status AS StatusCode,
( CASE
WHEN User.Name IS NULL THEN 'System'
ELSE User.Name
END ) AS `Username`,
ReadyLog.`Timestamp` AS `Received`,
DataSourceActivityLog.`Timestamp`
FROM DataSource
LEFT JOIN DataSourceActivityLog AS ReadyLog
ON ( ( ReadyLog.DataSourceId = DataSource.Id
AND ReadyLog.Description = 'ready' )
OR ( ReadyLog.DataSourceId = DataSource.RootId
AND ReadyLog.Description = 'ready' ) ),
DataSourceActivityLog
LEFT JOIN USER
ON USER.Id = DataSourceActivityLog.UserId,
Workflow
WHERE DataSource.Id IN ( 138, 139, 140, 141,
142, 143, 144, 145,
146, 147, 148, 149,
150, 151, 152, 153,
154, 155, 156, 157,
158, 160, 162, 163,
166, 167, 169, 170,
171, 173, 174, 176,
177, 179, 180, 182,
183, 185, 186, 187,
189, 190, 191, 193,
194, 196, 197, 199,
200, 201, 203, 204,
207, 208, 209, 211,
212, 214, 216, 217,
219, 221, 222, 223,
226, 227, 228, 231,
232, 233, 235, 237,
238, 242, 243, 240,
245, 246, 248, 250,
252, 253, 255, 256,
258, 259, 261, 263,
264, 266, 267, 271,
269, 272, 276, 274,
277, 280, 282, 284,
279, 287, 285, 288,
290, 291, 293, 298,
301, 299, 303, 304,
306, 309, 310, 311,
315, 316, 318, 322,
323, 325, 329, 330,
331, 336, 339, 343,
345, 346, 348, 351,
352, 354, 356, 357,
358, 360, 362, 364,
367, 369, 370, 373,
375, 376, 378, 381,
382, 384, 386, 388,
390, 391, 394, 395,
397, 400, 402, 404,
405, 408, 412, 413,
414, 415, 420, 421,
424, 425, 429, 430,
433, 434, 438, 439,
441, 442, 443, 445,
446, 447, 449, 451,
452, 453, 456, 457,
458, 459, 462, 464,
465, 466, 470, 473,
474, 475, 477, 478,
481, 482, 483, 485,
487, 488, 489, 491,
493, 494, 495, 497,
498, 500, 501, 502,
504, 505, 507, 508,
509, 512, 513, 514,
515, 516, 518, 519,
520, 521, 522, 524,
525, 526, 527, 529,
530, 531, 532, 534,
535, 536, 537, 539,
540, 541, 542, 544,
545, 546, 547, 549,
550, 551, 552, 553,
554, 556, 557, 559,
560, 561, 562, 564,
565, 566, 568, 569,
570, 571, 572, 574,
575, 576, 577, 579,
580, 581, 582, 583,
585, 586, 587, 588,
590, 591, 592, 593,
594, 596, 597, 598,
599, 601, 602, 603,
604, 606, 607, 608,
609, 611, 612, 613,
614, 616, 617, 618,
620, 621, 622, 623,
625, 626, 627, 628,
629, 631, 632, 633,
634, 636, 637, 638,
639, 641, 642, 643,
644, 646, 647, 648,
649, 651, 652, 653,
654, 656, 657, 658,
659, 660, 662, 663,
664, 665, 667, 668,
669, 670, 671, 673,
674, 675, 676, 678,
679, 680, 682, 683,
684, 686, 687, 688,
689, 691, 692, 693,
694, 697, 698, 699,
702, 703, 704, 707,
708, 709, 710, 712,
713, 714, 717, 718,
719, 720, 721, 724,
725, 726, 728, 729,
730, 734, 735, 736,
738, 739, 740, 742,
743, 744, 747, 748,
749, 751, 752, 753,
755, 756, 757, 759,
760, 761, 763, 764,
765, 767, 768, 769,
771, 772, 773, 775,
776, 777, 779, 780,
781, 782, 784, 785,
786, 788, 789, 790,
791, 793, 794, 795,
797, 798, 799, 800,
802, 803, 804, 807,
813, 814, 815, 816,
818, 822, 823, 824,
825, 830, 831, 832,
834, 835, 836, 837,
839, 840, 841, 842,
844, 845, 846, 848,
849, 850, 852, 853,
855, 856, 858, 859,
860, 862, 863, 864,
866, 867, 868, 870,
871, 872, 874, 875,
876, 877, 879, 880,
881, 883, 884, 886,
888, 889, 891, 892,
893, 895, 899, 900,
902, 903, 905, 906,
908, 909, 911, 912,
914, 915, 917, 918,
920, 921, 923, 925,
927, 929, 931, 932,
934, 936, 938, 940,
942, 944, 946, 948,
950, 952, 953, 955,
956, 958, 959, 961,
962, 963, 965, 966,
968, 969, 971, 972,
974, 975, 977, 978,
979, 981, 982, 983,
987, 988, 991, 992,
994, 995, 996, 998,
1000, 1001, 1002, 1003,
1005, 1007, 1008, 1009,
1011, 1013, 1014, 1016,
1017, 1019, 1020, 1022,
1023, 1024, 1025, 1028,
1029, 1032, 1033, 1035,
1036, 1038, 1040, 1041,
1043, 1045, 1046, 1049,
1050, 1052, 1053, 1055,
1056, 1059, 1060, 1063,
1064, 1067, 1068, 1070,
1071, 1073, 1074, 1076,
1077, 1079, 1080, 1082,
1083, 1087, 1088, 1090,
295, 296, 338, 1551,
1552, 1554, 1556, 1559,
1561, 1563, 1565, 1567,
1568, 1570, 1572, 1574,
1576, 1578, 1580, 1581,
1583, 1585, 1587, 1589,
1591, 1593, 1595, 1597,
1599, 1601, 1603, 1605,
1607, 1609, 1611, 1613,
1614, 1617, 1618, 1621,
1622, 1625, 1626, 1629,
1630, 1634, 1638, 1639,
1642, 1643, 1645, 1646,
1651, 1652, 1657, 1658,
1662, 1664, 1666, 1669,
1672, 1676, 1674, 1677,
1680, 1681, 1684, 1685,
1689, 1690, 1694, 1698,
1704, 1706, 1709, 1712, 1633 )
AND DataSourceActivityLog.Id = (SELECT DataSourceActivityLog.Id
FROM DataSourceActivityLog
WHERE DataSourceActivityLog.DataSourceId = DataSource.Id
AND ( DataSourceActivityLog.Description = 'data_entry_ready' )
ORDER BY TIMESTAMP DESC
LIMIT 1)
AND ( DataSource.Status = '103' )
AND Workflow.Id = 14
AND DataSourceActivityLog.`DataSourceId` = DataSource.`Id`
AND DataSource.`WorkflowId` = Workflow.`Id`
AND DataSource.IsDeleted = 0) AS WrappedData
ORDER BY WrappedData.`Timestamp` ASC
LIMIT 0, 1
Upvotes: 1
Views: 298
Answers (2)
Xint0
Reputation: 5399
I would try the following, first put the Id's of the IN clause in a temp table (TempIds); if you can be certain that a record with Id=14 exists in table Workflow, then you can remove the INNER JOIN to Workflow table and leave a simple condition of DataSource.WorkflowId = 14 in the WHERE clause; then change the order of the joins so that INNER JOIN are evaluated first, then LEFT JOIN; simplify the first LEFT JOIN expression and move the invariant condition ReadyLog.Description = 'ready' out of the OR:
SELECT DataSource.Id,
DataSourceActivityLog.Description,
DataSourceActivityLog.UserId,
DataSource.Status AS StatusCode,
( CASE
WHEN User.Name IS NULL THEN 'System'
ELSE User.Name
END ) AS `Username`,
ReadyLog.`Timestamp` AS `Received`,
DataSourceActivityLog.`Timestamp`
FROM
TempIds INNER JOIN
DataSource ON TempIds.Id = DataSource.Id INNER JOIN
DataSourceActivityLog ON DataSourceActivityLog.DataSourceId = DataSource.Id
AND DataSourceActivityLog.Id = (
SELECT DataSourceActivityLog.Id
FROM DataSourceActivityLog
WHERE DataSourceActivityLog.Description = 'data_entry_ready'
AND DataSourceActivityLog.DataSourceId = DataSource.Id
ORDER BY TIMESTAMP DESC
LIMIT 1) LEFT JOIN
DataSourceActivityLog AS ReadyLog ON ReadyLog.Description = 'ready'
AND (ReadyLog.DataSourceId = DataSource.Id
OR ReadyLog.DataSourceId = DataSource.RootId) LEFT JOIN
USER ON USER.Id = DataSourceActivityLog.UserId
WHERE
DataSource.Status = '103'
AND DataSource.WorkflowId = 14
AND DataSource.IsDeleted = 0
ORDER BY DataSourceActivityLog.`Timestamp` ASC
LIMIT 0, 1;
Finally you should consider adding the following index to DataSourceActivityLog table:
(Description ASC, DataSourceId ASC)
Upvotes: 0
NullRef
Reputation: 3743
First off it seems odd that this is one big subquery. Why can't you do this:
SELECT DataSource.Id,
DataSourceActivityLog.Description,
DataSourceActivityLog.UserId,
DataSource.Status AS StatusCode,
( CASE
WHEN User.Name IS NULL THEN 'System'
ELSE User.Name
END ) AS `Username`,
ReadyLog.`Timestamp` AS `Received`,
DataSourceActivityLog.`Timestamp`
FROM DataSource
LEFT JOIN DataSourceActivityLog AS ReadyLog
ON ( ( ReadyLog.DataSourceId = DataSource.Id
AND ReadyLog.Description = 'ready' )
OR ( ReadyLog.DataSourceId = DataSource.RootId
AND ReadyLog.Description = 'ready' ) ),
DataSourceActivityLog
LEFT JOIN USER
ON USER.Id = DataSourceActivityLog.UserId,
Workflow
WHERE DataSource.Id IN (... )
AND DataSourceActivityLog.Id = (SELECT DataSourceActivityLog.Id
FROM DataSourceActivityLog
WHERE DataSourceActivityLog.DataSourceId = DataSource.Id
AND ( DataSourceActivityLog.Description = 'data_entry_ready' )
ORDER BY TIMESTAMP DESC
LIMIT 1)
AND ( DataSource.Status = '103' )
AND Workflow.Id = 14
AND DataSourceActivityLog.`DataSourceId` = DataSource.`Id`
AND DataSource.`WorkflowId` = Workflow.`Id`
AND DataSource.IsDeleted = 0
ORDER BY DataSourceActivityLog.`Timestamp` ASC
LIMIT 0, 1
I suspect, but may be wrong, that WrappedData is derived that the ORDER BY is inefficient as it can't use any index and must wait for the entire query to complete before sorting.
Next, it seems some where clauses should be move into joins like this:
SELECT DataSource.Id,
DataSourceActivityLog.Description,
DataSourceActivityLog.UserId,
DataSource.Status AS StatusCode,
( CASE
WHEN User.Name IS NULL THEN 'System'
ELSE User.Name
END ) AS `Username`,
ReadyLog.`Timestamp` AS `Received`,
DataSourceActivityLog.`Timestamp`
FROM DataSource
LEFT JOIN DataSourceActivityLog AS ReadyLog
ON ( ( ReadyLog.DataSourceId = DataSource.Id
AND ReadyLog.Description = 'ready' )
OR ( ReadyLog.DataSourceId = DataSource.RootId
AND ReadyLog.Description = 'ready' ) ),
INNER JOIN DataSourceActivityLog ON DataSourceActivityLog.`DataSourceId` = DataSource.`Id`
AND DataSourceActivityLog.Id = (SELECT DataSourceActivityLog.Id
FROM DataSourceActivityLog
WHERE DataSourceActivityLog.DataSourceId = DataSource.Id
AND ( DataSourceActivityLog.Description = 'data_entry_ready' )
ORDER BY TIMESTAMP DESC
LIMIT 1)
LEFT JOIN USER
ON USER.Id = DataSourceActivityLog.UserId
INNER JOIN Workflow ON DataSource.`WorkflowId` = Workflow.`Id`
WHERE DataSource.Id IN (... )
AND ( DataSource.Status = '103' )
AND Workflow.Id = 14
AND DataSource.IsDeleted = 0
ORDER BY DataSourceActivityLog.`Timestamp` ASC
LIMIT 0, 1
Also, the aliased DataSourceActivityLog AS ReadyLog looks like it is returning quite a few rows. Should this be an INNER JOIN rather than a LEFT JOIN? Hard to tell based on what you have provided.
Finally, the DataSource.Id IN (... ) is so long it might benefit you to put these IDs into a lookup table that you can reference via a join rather than spilling them all out in the where clause.
I know this doesn't help you with EXPLAIN but honestly I don't think it is giving you much to go on in this case.
Upvotes: 5