MySQL Forums
Forum List  »  Newbie

Query involving 1160 topic id's examining 8160 rows, why? (optimization) who lies slow or explain
Posted by: Jan Kovalsky
Date: October 31, 2009 04:27AM

In below query, we got a lot of topic_id's (which are result of matched search phrase id's). A lot means exactly 1160 and explain showing that we touching same 1160, but slow-query-log showing we touching 8160 rows.

Which is truth?

Aditionally, why this query so slow ( 4 seconds because 6 lock time ) when we only grabbing with this LIMIT 0, 40; ? Is this LIMIT working at all when WHERE IN involved?

How to optimize this, i mean not only tweaking query but maybe changing method of grabbing data from searched topic'id.


# Time: 091031 11:11:08
# User@Host: root[root] @ localhost []
# Query_time: 10  Lock_time: 6  Rows_sent: 40  Rows_examined: 8160
SELECT t.*, f.forum_id, f.forum_name, u.user_rank, u.user_posts, u2.user_rank AS rank2, u2.user_posts AS posts2, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
                                FROM phpbb_topics t, phpbb_forums f, phpbb_users u, phpbb_posts p, phpbb_posts p2, phpbb_users u2
                                WHERE t.topic_id IN (528009, 529112, 531962, 537445, 542049, 542224, 542801, 543612, 543709, 554007, 572136, 577326, 581907, 582728, 583512, 583533, 584043, 600088, 607491, 609242, 613757, 623523, 623588, 627966, 633986, 634726, 641259, 641268, 641659, 646117, 646933, 647870, 648967, 650273, 650804, 651163, 652915, 653784, 654498, 661718, 662483, 662950, 663188, 664650, 665433, 667860, 669034, 669613, 669820, 670207, 670422, 670651, 671144, 671645, 672944, 673281, 674351, 674553, 675020, 678289, 678291, 680163, 683002, 684723, 684813, 686431, 689133, 691725, 691744, 691789, 693806, 694742, 694946, 695378, 696770, 697394, 697473, 697601, 698084, 698531, 700569, 700647, 700785, 701228, 702209, 702309, 703213, 703300, 704591, 705579, 705665, 705795, 705863, 707954, 707974, 708515, 708942, 709220, 709532, 709561, 710789, 710912, 711106, 711152, 711880, 712238, 714694, 714738, 714855, 715371, 716170, 716355, 716389, 717334, 717377, 717729, 718190, 719085, 719553, 719717, 719764, 720096, 720241, 720427, 720437, 721403, 721748, 721875, 722202, 722875, 722988, 723075, 723723, 723729, 723765, 724689, 724903, 725335, 725618, 726286, 727387, 727514, 727848, 728715, 728799, 729130, 729651, 729787, 729981, 730674, 731190, 731322, 731975, 732109, 732730, 732820, 733535, 734191, 735764, 735860, 736461, 736626, 736803, 737849, 738568, 739059, 739578, 741547, 741614, 742501, 742631, 743109, 743629, 744678, 745769, 746112, 746739, 748056, 748319, 748477, 748528, 748539, 748723, 749367, 749502, 749571, 749781, 749848, 749962, 750492, 750619, 752266, 752737, 754409, 755321, 756231, 756354, 757048, 757503, 758069, 758523, 759312, 759601, 761211, 761453, 761990, 764035, 764130, 764765, 766212, 766897, 768091, 768142, 770499, 770965, 772560, 774381, 774607, 776149, 776865, 778803, 778867, 778977, 779194, 780037, 780157, 780387, 780803, 782309, 782788, 784965, 784975, 785740, 787583, 788086, 788888, 788902, 790245, 790770, 791355, 792195, 793008, 793859, 794941, 795750, 795968, 796997, 798115, 798122, 798156, 798713, 799048, 799282, 800114, 800304, 800396, 800613, 801648, 801721, 803066, 803095, 807480, 808001, 808222, 808645, 808685, 811883, 811908, 812096, 812927, 814541, 816226, 816554, 817560, 817640, 820211, 820379, 821820, 821833, 823477, 825616, 826286, 827065, 827129, 828125, 830233, 830411, 831261, 831334, 831366, 831862, 832549, 833669, 833851, 834348, 835133, 835212, 836760, 836792, 836926, 837452, 838090, 838208, 840296, 840960, 841124, 841211, 841913, 842154, 842703, 842830, 843520, 844189, 844612, 845702, 845725, 846135, 846398, 846453, 846620, 847173, 847391, 849769, 850061, 850301, 850867, 851722, 851855, 854494, 854535, 855179, 855475, 857492, 858249, 858406, 858409, 859289, 859727, 860643, 863141, 863542, 865149, 865224, 865245, 866638, 866799, 867451, 867952, 868200, 868582, 869649, 870418, 870495, 870507, 870903, 870981, 871910, 872562, 872782, 873018, 873356, 874416, 874882, 874958, 874961, 876209, 876602, 877010, 877089, 877121, 877729, 878708, 879028, 879832, 880733, 881080, 882521, 884503, 885158, 885302, 885614, 885749, 885807, 886010, 886176, 886915, 887140, 887457, 888654, 889533, 891197, 891202, 891441, 893640, 895659, 896389, 896423, 896772, 897131, 897235, 898217, 900179, 900423, 901527, 901725, 902140, 903069, 903865, 903871, 903909, 904625, 904878, 905047, 905607, 906396, 906397, 909013, 909214, 910093, 910830, 911147, 911446, 911862, 911958, 912063, 913216, 913253, 913807, 914120, 914421, 914644, 915211, 915312, 915358, 915518, 916056, 916493, 917001, 918147, 919139, 919687, 920477, 920878, 921946, 922079, 922135, 923439, 923513, 923679, 923836, 924090, 924172, 924206, 924693, 925271, 925288, 925460, 926141, 926266, 927109, 927310, 927710, 928096, 929774, 931060, 933756, 933819, 933876, 933902, 934137, 934645, 934683, 934852, 934928, 935402, 936745, 936802, 937518, 938357, 939197, 939592, 940444, 940537, 941321, 941356, 942093, 942256, 942554, 942644, 943233, 944375, 944612, 945025, 945616, 946192, 946666, 947037, 947874, 948996, 949483, 950800, 950963, 951494, 953167, 953748, 954236, 954714, 954821, 955025, 955236, 955388, 956098, 956943, 957435, 958540, 958627, 959494, 959703, 960653, 961216, 961289, 961639, 962047, 962977, 963103, 963792, 964317, 966484, 966762, 967169, 967489, 967587, 970659, 970800, 970921, 971508, 971566, 972587, 975034, 975837, 978573, 980482, 980960, 982504, 982541, 982564, 982759, 982801, 982975, 983026, 983556, 983701, 983779, 984093, 984223, 984245, 984350, 984519, 985173, 985727, 986022, 986119, 986760, 986996, 987013, 987177, 987542, 988324, 988529, 989516, 990801, 991276, 992964, 993094, 993697, 993857, 994596, 995023, 995738, 995762, 995886, 996278, 996450, 996782, 998288, 999803, 999957, 1000010, 1002329, 1002418, 1003756, 1004788, 1005019, 1005656, 1008812, 1009900, 1010968, 1012537, 1013002, 1013388, 1014018, 1019330, 1019591, 1021288, 1021330, 1023433, 1023890, 1024174, 1024208, 1024358, 1025455, 1027056, 1028695, 1030641, 1031253, 1031898, 1033659, 1034636, 1035121, 1036175, 1037722, 1038150, 1040049, 1042647, 1044523, 1045227, 1047669, 1048128, 1048648, 1048797, 1051189, 1051323, 1053592, 1067142, 1067781, 1067963, 1071115, 1072528, 1073848, 1075316, 1076577, 1080181, 1081202, 1082705, 1083677, 1086661, 1089788, 1101172, 1102187, 1102247, 1105293, 1105378, 1106940, 1108081, 1109728, 1109929, 1110064, 1111540, 1112043, 1113648, 1113775, 1115155, 1116066, 1117760, 1118669, 1119293, 1121615, 1124334, 1127138, 1129251, 1132681, 1132742, 1133365, 1134292, 1136452, 1141047, 1141375, 1144748, 1146583, 1147159, 1149696, 1151179, 1152895, 1153719, 1154901, 1156392, 1158837, 1159117, 1159282, 1159657, 1160720, 1163188, 1163276, 1164289, 1168247, 1174178, 1174681, 1175924, 1176929, 1178480, 1179015, 1182359, 1184218, 1184376, 1184688, 1185969, 1186529, 1186616, 1187133, 1188555, 1188842, 1192186, 1199526, 1200584, 1200625, 1201574, 1206629, 1208690, 1208961, 1209594, 1210030, 1213085, 1216872, 1217664, 1219556, 1226329, 1226402, 1226969, 1226974, 1229220, 1230186, 1230247, 1232001, 1235783, 1236591, 1237954, 1244474, 1246356, 1246962, 1254088, 1256685, 1260279, 1261145, 1261603, 1262829, 1265454, 1267027, 1268965, 1269193, 1269821, 1272870, 1274964, 1275438, 1277366, 1279860, 1283528, 1285546, 1289872, 1289909, 1290895, 1292057, 1292625, 1294673, 1294783, 1296873, 1304676, 1306053, 1307773, 1309082, 1310051, 1312313, 1313608, 1315281, 1315792, 1315839, 1318508, 1320347, 1322056, 1323436, 1325016, 1328381, 1330357, 1330490, 1330498, 1338159, 1338478, 1338596, 1338618, 1345064, 1345480, 1348297, 1348724, 1351574, 1352582, 1365388, 1370807, 1372075, 1372807, 1373163, 1373261, 1374973, 1374978, 1375608, 1375852, 1378452, 1380121, 1380887, 1381783, 1388969, 1389882, 1395757, 1399179, 1400335, 1405659, 1406213, 1407059, 1407651, 1410799, 1411362, 1411538, 1413206, 1413627, 1415986, 1416538, 1419450, 1419679, 1419860, 1420133, 1420709, 1421079, 1423113, 1423115, 1423120, 1423574, 1424225, 1428864, 1434238, 1434708, 1438207, 1438336, 1438356, 1440649, 1441548, 1441640, 1443361, 1444281, 1444999, 1445741, 1446740, 1447544, 1447565, 1451043, 1451664, 1454137, 1457425, 1457532, 1457822, 1459396, 1459660, 1460583, 1464180, 1464222, 1464483, 1464525, 1465734, 1466107, 1469276, 1469331, 1469809, 1470385, 1470734, 1471286, 1472655, 1473491, 1473540, 1476396, 1476551, 1476801, 1478260, 1478439, 1479066, 1479839, 1479948, 1480480, 1480522, 1480543, 1483111, 1483723, 1483835, 1483922, 1484108, 1484504, 1484564, 1484686, 1484807, 1484898, 1485324, 1485464, 1485795, 1487785, 1489922, 1490335, 1493223, 1493443, 1496081, 1496511, 1497225, 1497261, 1498210, 1498497, 1499441, 1500444, 1502387, 1502796, 1503752, 1504458, 1504709, 1506193, 1506221, 1506293, 1508274, 1510407, 1511296, 1515005, 1515754, 1519374, 1524436, 1527702, 1528999, 1529487, 1536795, 1536799, 1536802, 1536808, 1540228, 1540492, 1541636, 1542741, 1543547, 1543768, 1544175, 1548178, 1554127, 1554957, 1554990, 1557474, 1559145, 1560654, 1561712, 1562965, 1564558, 1564633, 1564953, 1565866, 1566543, 1566704, 1567369, 1567714, 1567732, 1568338, 1568511, 1570920, 1573387, 1581178, 1581538, 1582404, 1582621, 1583286, 1585592, 1586171, 1586315, 1586657, 1589601, 1591066, 1591137, 1591231, 1593939, 1594216, 1594584, 1595002, 1599492, 1601689, 1602390, 1602923, 1605344, 1606150, 1606173, 1606341, 1606348, 1606393, 1609200, 1610123, 1610674, 1611406, 1613548, 1614381, 1615989, 1618369, 1621536, 1622294, 1625045, 1625136, 1625288, 1626539, 1626550, 1626728, 1627062, 1629384, 1630088, 1636936, 1639640, 1639966, 1640940, 1641152, 1641324, 1642173, 1642261, 1642274, 1644230, 1644596, 1646035, 1648120, 1648999, 1650041, 1651138, 1652371, 1654316, 1656897, 1656924, 1659104, 1660734, 1661200, 1662228, 1663441, 1665176, 1669948, 1670225, 1670244, 1670571, 1670835, 1672296, 1675143, 1676745, 1676848, 1679196, 1680316, 1682440, 1688163, 1691037, 1694136, 1695124, 1697193, 1697736, 1699712, 1702069, 1702336, 1703476, 1703514, 1705066, 1705662, 1707037, 1707218, 1707577, 1708794, 1709730, 1710609, 1710676, 1710867, 1715594, 1716112, 1717608, 1717931, 1723868, 1724012, 1726928, 1727244, 1727445, 1728279, 1729564, 1729908, 1730628, 1730803, 1730828, 1732653, 1734737, 1738772, 1738994, 1739270, 1740326, 1743889, 1743922, 1746136, 1751358, 1751613, 1752320, 1752505, 1753074, 1753132, 1754058, 1754747, 1754902, 1755381, 1755735, 1755941, 1756004, 1756442, 1756884, 1759296, 1759476, 1760003, 1760823, 1763000, 1764554, 1764985, 1766522, 1766682, 1766805, 1767054, 1767102, 1767185, 1767349, 1767481, 1767916, 1769642, 1769716, 1770489, 1770607, 1770694, 1774244, 1774451, 1774614, 1775624, 1776110, 1776430, 1776967, 1777632, 1777854, 1778969, 1779217, 1779224, 1779515, 1779556, 1780215, 1781431, 1783695, 1783752, 1785231, 1788009, 1791483, 1792290, 1793865, 1793983, 1795486, 1796242, 1796885, 1797025, 1797091, 1797317, 1797354, 1797579)
                                        AND t.topic_poster = u.user_id
                                        AND f.forum_id = t.forum_id
                                        AND p.post_id = t.topic_first_post_id
                                        AND p2.post_id = t.topic_last_post_id
                                        AND u2.user_id = p2.poster_id ORDER BY p2.post_time DESC LIMIT 0, 40;



------------------------------+
| id | select_type | table | type   | possible_keys                                   | key     | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------+--------+-------------------------------------------------+---------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | t     | range  | PRIMARY,forum_id,topic_last_post_id,fid_type_lp | PRIMARY | 3       | NULL                     | 1160 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                                         | PRIMARY | 2       | t.forum_id            |    1 |                                              |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                                         | PRIMARY | 3       | t.topic_poster        |    1 |                                              |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY                                         | PRIMARY | 3       | t.topic_first_post_id |    1 |                                              |
|  1 | SIMPLE      | p2    | eq_ref | PRIMARY,poster_id                               | PRIMARY | 3       | t.topic_last_post_id  |    1 |                                              |
|  1 | SIMPLE      | u2    | eq_ref | PRIMARY                                         | PRIMARY | 3       | p2.poster_id          |    1 |                                              |
+----+-------------+-------+--------+-------------------------------------------------+---------+---------+--------------------------+------+----------------------------------------------+

Options: ReplyQuote


Subject
Written By
Posted
Query involving 1160 topic id's examining 8160 rows, why? (optimization) who lies slow or explain
October 31, 2009 04:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.