Joomla 1.5: improving menu related query

Question

I have a joomla 1.5.10 based big system. I am using PHP 5.3.13 and MySQL 5.1.58 version. During peak load my system runs very slow and break sometimes. When I analyzed, I found multiple queries are logging in slow query logs. One among them related with menu section of joomla is below:

SELECT id FROM jos_menu WHERE menutype = 'mainmenu' AND parent in ( 251,252,253,271,295,296,297,298,299,300,312,347,348,349,350,351,352,356,357,358,389,390,396,397,401,406,411,518,527,582,633,680,681,682,683,684,685,686,689,701,702,703,708,711,743,744,745,746,747,748,749,750,752,761,763,764,808,827,828,829,833,835,838,839,848,904,910,911,912,913,914,915,916,917,933,934,937,957,958,961,962,963,964,965,1029,1030,1035,1041,1043,1044,1045,1047,1071,1104,1107,1109,1110,1116,1118,1126,1131,1135,1137,1173,1174,1175,1176,1178,1181,1182,1183,1189,1190,1192,1193,1194,1196,1208,1220,1262,1263,1265,1267,1268,1269,1270,1271,1273,1314,1339,1340,1341,1374,1380,1403,1405,1406,1407,1447,1455,1456,1458,1476,1477,1478,1479,1480,1481,1518,1522,1523,1524,1529,1543,1562,1586,1598,1599,1600,1601,1611,1612,1615,1627,1628,1636,1637,1638,1639,1640,1641,1645,1646,1667,1685,1689,1690,1691,1692,1702,1707,1741,1752,1766,1777,1778,1779,1853,1854,3869,3877,3884,3885,3887,3888,3889,3892,3897,3928,3941,3962,3973,3976,4139,4140,4158,4166,4167,4254,4256,4257,4258,4259,4264,4266,4283,4300,4327,4329,4350,4487,4893,4896,5219,5220,5221,5222,5223,5240,5254,5305,5308,5317,5336,5342,5348,5375,5400,5401,5402,5403,5405,5407,5416,5417,5418,5419,5420,5471,5475,5511,5512,5513,5515,5516,5530,5533,5538,5563,5582,5592,5594,5598,5599,5600,5604,5605,5606,5607,5608,5609,5610,5611,5618,5643,5644,5646,5647,5649,5674,5683,5684,5694,5695,5696,5704,5706,5707,5736,5743,5768,5769,5770,5771,5772,5791,5799,5821,5823,5824,5833,5836,5892,5903,5908,5909,5912,5913,5914,5915,5916,5921,5944,5960,5961,5962,5970,5971,5972,5973,5984,6002,6063,6064,6065,6066,6067,6070,6071,6072,6073,6074,6088,6132,6139,6146,6150,6173,6182,6187,6189,6193,6194,6196,6197,6204,6208,6210,6211,6212,6213,6221,6226,6237,6238,6244,6246,6247,6249,6252,6294,6305,6306,6315,6408,6409,6410,6411,6412,6413,6414,6415,6416,6461,6663,6673,6702,6703,6726,6727,6728,6729,6730,6731,6744,6750,6769,6770,6774,6777,6792,6793,6803,6839,6841,6849,6851 )

Below is the explain result of this query:

           id: 1
  select_type: SIMPLE
        table: jos_menu
         type: range
possible_keys: menutype,Index_parent
          key: Index_parent
      key_len: 4
          ref: NULL
         rows: 753
        Extra: Using where
1 row in set (0.00 sec)

I am not so expert in performance tuning task. How can I get rid from bad performance of my system?

Answer 1

One thing you can do is to add proper indexes. Add an index for the 'parent' column and see it will improve the performance.