[FIXED] 32723 queries in main-menu->new->link - content item

[speeduneed]

Insane page load 32723 queries executed in main-menu->new->link - content item???????
I have a ton of articles in a database. Apparently the item->content item link loads a funciton that make a query for every item rather than making 1 query for all the info.... Anyone have a fix for this?



Page was generated in 0.012210 seconds
32723 queries executed

1
SET sql_mode = 'MYSQL40'2
SELECT template
FROM jos_templates_menu
WHERE client_id = 1
AND menuid = 03
DELETE FROM jos_session
WHERE time 04
UPDATE jos_session
SET time = '1175479689'
WHERE session_id = '41c5dd2d129db61c8c5a93e2ec899e90'5
SELECT COUNT( session_id )
FROM jos_session
WHERE session_id = '41c5dd2d129db61c8c5a93e2ec899e90'
AND username = 'pkruger'
AND userid = 636
SELECT *
FROM jos_menu
WHERE id = '0'7
SELECT a.id AS value, a.title AS text, a.sectionid, a.catid
FROM jos_content AS a
INNER JOIN jos_categories AS c ON a.catid = c.id
INNER JOIN jos_sections AS s ON a.sectionid = s.id
WHERE a.state = 1
ORDER BY a.sectionid, a.catid, a.title8
SELECT s.title
FROM jos_sections AS s
WHERE s.scope = 'content'
AND s.id = 19
SELECT c.title
FROM jos_categories AS c
WHERE c.id = 2110
SELECT s.title
FROM jos_sections AS s
WHERE s.scope = 'content'
AND s.id = 1

[Websmurf]

I'm afraid you are quite correct

when adding a new menuitem, the following piece of code will be executed:

Code: Select all

$query = "SELECT a.id AS value, a.title AS text, a.sectionid, a.catid "
			. "\n FROM #__content AS a"
			. "\n INNER JOIN #__categories AS c ON a.catid = c.id"
			. "\n INNER JOIN #__sections AS s ON a.sectionid = s.id"
			. "\n WHERE a.state = 1"
			. "\n ORDER BY a.sectionid, a.catid, a.title"
			;
			$database->setQuery( $query );
			$contents = $database->loadObjectList( );

			foreach ( $contents as $content ) {
				$query = "SELECT s.title"
				. "\n FROM #__sections AS s"
				. "\n WHERE s.scope = 'content'"
				. "\n AND s.id = " . (int) $content->sectionid
				;
				$database->setQuery( $query );
				$section = $database->loadResult();

				$query = "SELECT c.title"
				. "\n FROM #__categories AS c"
				. "\n WHERE c.id = " . (int) $content->catid
				;
				$database->setQuery( $query );
				$category = $database->loadResult();

				$value = $content->value;
				$text = $section ." - ". $category ." / ". $content->text ."    ";

				$temp[] = mosHTML::makeOption( $value, $text );
				$contents = $temp;
			}

Resulting in 2 queries for each content item.

Will see if I can come up with something better than that

[Websmurf]

Okidoki,

Open /administrator/components/com_menus/content_item_link.class.php

Find:

Code: Select all

			$query = "SELECT a.id AS value, a.title AS text, a.sectionid, a.catid "
			. "\n FROM #__content AS a"
			. "\n INNER JOIN #__categories AS c ON a.catid = c.id"
			. "\n INNER JOIN #__sections AS s ON a.sectionid = s.id"
			. "\n WHERE a.state = 1"
			. "\n ORDER BY a.sectionid, a.catid, a.title"
			;
			$database->setQuery( $query );
			$contents = $database->loadObjectList( );

			foreach ( $contents as $content ) {
				$query = "SELECT s.title"
				. "\n FROM #__sections AS s"
				. "\n WHERE s.scope = 'content'"
				. "\n AND s.id = " . (int) $content->sectionid
				;
				$database->setQuery( $query );
				$section = $database->loadResult();

				$query = "SELECT c.title"
				. "\n FROM #__categories AS c"
				. "\n WHERE c.id = " . (int) $content->catid
				;
				$database->setQuery( $query );
				$category = $database->loadResult();

				$value = $content->value;
				$text = $section ." - ". $category ." / ". $content->text ."    ";

				$temp[] = mosHTML::makeOption( $value, $text );
				$contents = $temp;
			}

Replace with:

Code: Select all

			  $query = 'SELECT a.id AS value, CONCAT(s.title, \' - \',c.title,\' / \',a.title, \'    \') AS text
			  FROM #__content AS a
			  INNER JOIN #__categories AS c ON a.catid = c.id
			  INNER JOIN #__sections AS s ON a.sectionid = s.id AND s.scope = \'content\'
			  WHERE a.state = 1
			  ORDER BY a.sectionid, a.catid, a.title';
			$database->setQuery($query);
			$contents = $database->loadObjectList();

That should do about the same in only one query.

[Tonie]

Moving to Q&T forum on request.

[Websmurf]

Tagged as confirmed, high priority since it should really be changed in a possible new release

[speeduneed]

much better !!!!! I have 15000 articles so this is actually loading now.


13 queries executed

1
SET sql_mode = 'MYSQL40'2
SELECT template
FROM jos_templates_menu
WHERE client_id = 1
AND menuid = 03
DELETE FROM jos_session
WHERE time 04
UPDATE jos_session
SET time = '1175576841'
WHERE session_id = '6f26fe993eecb479b8c192498a56f170'5
SELECT COUNT( session_id )
FROM jos_session
WHERE session_id = '6f26fe993eecb479b8c192498a56f170'
AND username = 'pkruger'
AND userid = 636
SELECT *
FROM jos_menu
WHERE id = '0'

[speeduneed]

Note to the test team.... The test site should have 100k articles in it. That way the test team can find this stuff. If the test team uses an empty joomla then no one will address its ability to handle a large volume of content.

Thanks for your help Websmurf

Speed

[Websmurf]

No problemo

[ianreds]

Hi,

  I have already posted problem before but not one has come up with a solution.

  When I enter the Module Manager, this is the debug output and everything is OK

16 queries executed
1
SELECT template
FROM jos_templates_menu
WHERE client_id = 1
AND menuid = 0

2
DELETE FROM jos_session
WHERE time 0

3
UPDATE jos_session
SET time = '1176386253'
WHERE session_id = '53978d9be0bd74a0d4dd80305a4c10b6'

4
SELECT COUNT( session_id )
FROM jos_session
WHERE session_id = '53978d9be0bd74a0d4dd80305a4c10b6'
AND username = 'eurep999'
AND userid = 62

5
SELECT COUNT(*)
FROM jos_modules AS m
WHERE m.client_id = 0

6
SELECT m.*, u.name AS editor, g.name AS groupname, MIN(mm.menuid) AS pages
FROM jos_modules AS m
LEFT JOIN jos_users AS u ON u.id = m.checked_out
LEFT JOIN jos_groups AS g ON g.id = m.access
LEFT JOIN jos_modules_menu AS mm ON mm.moduleid = m.id
WHERE m.client_id = 0
GROUP BY m.id
ORDER BY position ASC, ordering ASC
LIMIT 30

7
SELECT t.position AS value, t.position AS text
FROM jos_template_positions as t
LEFT JOIN jos_modules AS m ON m.position = t.position
WHERE m.client_id = 0
GROUP BY t.position
ORDER BY t.position

8
SELECT module AS value, module AS text
FROM jos_modules
WHERE client_id = 0
GROUP BY module
ORDER BY module

9
SELECT a.id, a.title, a.name
FROM jos_sections AS a
WHERE a.scope = 'content'
GROUP BY a.id
ORDER BY a.ordering

10
SELECT params
FROM jos_modules
WHERE module = 'mod_mainmenu'
ORDER BY title

11
SELECT menutype
FROM jos_menu
GROUP BY menutype
ORDER BY menutype

12
SELECT *
FROM jos_components
WHERE name != 'frontpage'
AND name != 'media manager'
ORDER BY ordering, name

13
SELECT id, title, module, position, content, showtitle, params
FROM jos_modules AS m
WHERE m.published = 1
AND m.position = 'header'
AND m.client_id = 1
ORDER BY m.ordering

14
SELECT COUNT(*)
FROM jos_messages
WHERE state = 0
AND user_id_to = 62

15
SELECT COUNT( session_id )
FROM jos_session
WHERE session_id != '53978d9be0bd74a0d4dd80305a4c10b6'

16
SELECT id, title, module, position, content, showtitle, params
FROM jos_modules AS m
WHERE m.published = 1
AND m.position = 'debug'
AND m.client_id = 1
ORDER BY m.ordering


When I select a module to edit, and go to the URL
          http://eureporter.co.uk/administrator/i ... nu=1&id=61
I get this error.

"The server encountered an unexpected condition which prevented it from fulfilling the request.
The script had an error or it did not produce any output. If there was an error, you should be able to see it in the error log. "

I have already posted this before but not one has come up with a solution. It appears that the problem is to do with the number of rows in the JOS_MENU table - if I insert over about 1200 rows, I get the below error - if I delete rows from the JOS_MENU table, I can edit modules .

As I have stated before, I do not understand why the number of rows in the JOS_MENU table is giving an error when trying to edit a module.

As this thread is about HIGH: sql executions , could this be a problem.

I have struggled to overcome this problem - the only solution at the present is to backup the JOS_MENU table, delete the rows, modify a module, then re-import the JOS_MENU table.

Any help please

Ian.

[Websmurf]

No, this is something specific to the menu component and not related to editing modules.

[ianreds]

Hi,

  OK - but how do I get around the problem.

  Do I reinstall the menu modules maybe ??

Ian.

[ianreds]

Hi,

  As another point, with regard the following statement

SELECT menutype
FROM jos_menu
GROUP BY menutype
ORDER BY menutype

why not

SELECT distinct(menutype)
FROM jos_menu
ORDER BY menutype

[RobS]

Fixed in SVN.  Thanks for reporting.