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

Confirmed bugs that have been Fixed - Joomla 1.0.x



[New Threads cannot be started in this forum]
Locked
User avatar
speeduneed
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 142
Joined: Wed Jul 05, 2006 5:27 pm
Contact:

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

Post by speeduneed » Sun Apr 01, 2007 2:15 pm

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
Last edited by Websmurf on Mon Apr 02, 2007 9:00 am, edited 1 time in total.

User avatar
Websmurf
Joomla! Hero
Joomla! Hero
Posts: 2230
Joined: Fri Aug 19, 2005 2:23 pm
Location: The Netherlands
Contact:

Re: Insane page load 32723 queries executed in main-menu->new->link - content it

Post by Websmurf » Mon Apr 02, 2007 8:26 am

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 :)
Adam van Dongen - Developer

- Blocklist, ODT Indexer, EasyFAQ, Easy Guestbook, Easy Gallery, YaNC & Redirect -
http://www.joomla-addons.org - http://www.bandhosting.nl

User avatar
Websmurf
Joomla! Hero
Joomla! Hero
Posts: 2230
Joined: Fri Aug 19, 2005 2:23 pm
Location: The Netherlands
Contact:

Re: Insane page load 32723 queries executed in main-menu->new->link - content item

Post by Websmurf » Mon Apr 02, 2007 8:32 am

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.
Adam van Dongen - Developer

- Blocklist, ODT Indexer, EasyFAQ, Easy Guestbook, Easy Gallery, YaNC & Redirect -
http://www.joomla-addons.org - http://www.bandhosting.nl

User avatar
Tonie
Joomla! Master
Joomla! Master
Posts: 16584
Joined: Thu Aug 18, 2005 7:13 am

Re: Insane page load 32723 queries executed in main-menu->new->link - content it

Post by Tonie » Mon Apr 02, 2007 8:37 am

Moving to Q&T forum on request.

User avatar
Websmurf
Joomla! Hero
Joomla! Hero
Posts: 2230
Joined: Fri Aug 19, 2005 2:23 pm
Location: The Netherlands
Contact:

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by Websmurf » Mon Apr 02, 2007 9:00 am

Tagged as confirmed, high priority since it should really be changed in a possible new release :)
Adam van Dongen - Developer

- Blocklist, ODT Indexer, EasyFAQ, Easy Guestbook, Easy Gallery, YaNC & Redirect -
http://www.joomla-addons.org - http://www.bandhosting.nl

User avatar
speeduneed
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 142
Joined: Wed Jul 05, 2006 5:27 pm
Contact:

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by speeduneed » Mon Apr 02, 2007 5:09 pm

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'

User avatar
speeduneed
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 142
Joined: Wed Jul 05, 2006 5:27 pm
Contact:

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by speeduneed » Mon Apr 02, 2007 5:16 pm

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

User avatar
Websmurf
Joomla! Hero
Joomla! Hero
Posts: 2230
Joined: Fri Aug 19, 2005 2:23 pm
Location: The Netherlands
Contact:

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by Websmurf » Mon Apr 02, 2007 5:40 pm

No problemo :)
Adam van Dongen - Developer

- Blocklist, ODT Indexer, EasyFAQ, Easy Guestbook, Easy Gallery, YaNC & Redirect -
http://www.joomla-addons.org - http://www.bandhosting.nl

ianreds
Joomla! Apprentice
Joomla! Apprentice
Posts: 18
Joined: Fri Jan 05, 2007 8:44 pm

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by ianreds » Thu Apr 12, 2007 2:07 pm

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.

User avatar
Websmurf
Joomla! Hero
Joomla! Hero
Posts: 2230
Joined: Fri Aug 19, 2005 2:23 pm
Location: The Netherlands
Contact:

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by Websmurf » Thu Apr 12, 2007 3:17 pm

No, this is something specific to the menu component and not related to editing modules.
Adam van Dongen - Developer

- Blocklist, ODT Indexer, EasyFAQ, Easy Guestbook, Easy Gallery, YaNC & Redirect -
http://www.joomla-addons.org - http://www.bandhosting.nl

ianreds
Joomla! Apprentice
Joomla! Apprentice
Posts: 18
Joined: Fri Jan 05, 2007 8:44 pm

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by ianreds » Fri Apr 13, 2007 8:20 pm

Hi,

  OK - but how do I get around the problem.

  Do I reinstall the menu modules maybe ??

Ian.

ianreds
Joomla! Apprentice
Joomla! Apprentice
Posts: 18
Joined: Fri Jan 05, 2007 8:44 pm

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by ianreds » Fri Apr 13, 2007 9:17 pm

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

User avatar
RobS
Joomla! Ace
Joomla! Ace
Posts: 1367
Joined: Mon Dec 05, 2005 10:17 am
Location: New Orleans, LA, USA
Contact:

Re: [HIGH:CONFIRMED:1.0.12] 32723 queries in main-menu->new->link - content item

Post by RobS » Mon May 14, 2007 9:41 pm

Fixed in SVN.  Thanks for reporting.
Rob Schley - Open Source Matters
Webimagery - http://www.webimagery.net/ - Professional Consulting Services
JXtended - http://www.jxtended.com/ - Free and Commercial Joomla! Extensions


Locked

Return to “Q&T 1.0.x Resolved”