performances and size of jos_content table issue
976 views
Skip to first unread message
Thierry bela nanga
Jun 27, 2011, 8:37:31 AM6/27/11
to joomla-...@googlegroups.com
I am having big performance issue with jos_content database queries as the jos_content table grows, this is espacially caused by the large number of tables in the join. the table have more than 70k rows.
is there any plan to adress such situations ?
here are some queries logged with mysql slow query log.
cheers,
# Query_time: 25 Lock_time: 0 Rows_sent: 20 Rows_examined: 440972
use mydatabase;
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down,l.title AS language_title,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name
FROM jos_content AS a
LEFT JOIN `jos_languages` AS l ON l.lang_code = a.language
LEFT JOIN jos_users AS uc ON uc.id=a.checked_out
LEFT JOIN jos_viewlevels AS ag ON ag.id = a.access
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.title asc LIMIT 0, 20;
# Query_time: 38 Lock_time: 0 Rows_sent: 13900 Rows_examined: 201790
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND( v.rating_sum / v.rating_count ) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN jos_contact_details AS contact on contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-06-27 12:07:31') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-06-27 12:07:31')
ORDER BY c.path, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created;
--
http://tbela99.blogspot.com/
fax : (+33) 08 26 51 94 51
is there any plan to adress such situations ?
here are some queries logged with mysql slow query log.
cheers,
# Query_time: 25 Lock_time: 0 Rows_sent: 20 Rows_examined: 440972
use mydatabase;
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down,l.title AS language_title,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name
FROM jos_content AS a
LEFT JOIN `jos_languages` AS l ON l.lang_code = a.language
LEFT JOIN jos_users AS uc ON uc.id=a.checked_out
LEFT JOIN jos_viewlevels AS ag ON ag.id = a.access
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.title asc LIMIT 0, 20;
# Query_time: 38 Lock_time: 0 Rows_sent: 13900 Rows_examined: 201790
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND( v.rating_sum / v.rating_count ) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN jos_contact_details AS contact on contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-06-27 12:07:31') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-06-27 12:07:31')
ORDER BY c.path, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created;
--
http://tbela99.blogspot.com/
fax : (+33) 08 26 51 94 51
Ofer Cohen
Jun 27, 2011, 11:13:38 AM6/27/11
to joomla-...@googlegroups.com
Hey Thierry,
Could you test the performance with the EXPLAIN mysql method?
Please provide the output of the command.
Thanks,
Ofer Cohen
--
You received this message because you are subscribed to the Google Groups "Joomla! CMS Development" group.
To post to this group, send an email to joomla-...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-cm...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-cms?hl=en-GB.
Thierry bela nanga
Jun 27, 2011, 1:37:27 PM6/27/11
to joomla-...@googlegroups.com
here you can see the Explain of each query, those queries come from the back of com_content and should not use so many tables in the join to avoid that situation
http://dl.dropbox.com/u/1731360/explain1.htm
http://dl.dropbox.com/u/1731360/explain2.htm
http://dl.dropbox.com/u/1731360/explain1.htm
http://dl.dropbox.com/u/1731360/explain2.htm
Thierry bela nanga
Jun 27, 2011, 2:18:56 PM6/27/11
to joomla-...@googlegroups.com
it gets even worse, more than 5minutes for a single request I'll need to disable sort in the com_content/article view while waiting for a good solution
# Query_time: 331.932000 Lock_time: 0.023000 Rows_sent: 0 Rows_examined: 136
use database;
SET timestamp=1309198477;
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND( v.rating_sum / v.rating_count ) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN jos_contact_details AS contact on contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-06-27 18:09:00') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-06-27 18:09:00')
ORDER BY c.path, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created;
# Query_time: 331.932000 Lock_time: 0.023000 Rows_sent: 0 Rows_examined: 136
use database;
SET timestamp=1309198477;
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND( v.rating_sum / v.rating_count ) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN jos_contact_details AS contact on contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
ORDER BY c.path, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created;
Mark Dexter
Jun 27, 2011, 6:25:43 PM6/27/11
to joomla-...@googlegroups.com
How long exactly does it take to load the page? In the administrative
back end, we aren't quite so worried about performance. Is the query
time in seconds? Thanks. Mark
back end, we aren't quite so worried about performance. Is the query
time in seconds? Thanks. Mark
Mark Dexter
Jun 27, 2011, 6:27:26 PM6/27/11
to joomla-...@googlegroups.com
OK. Please disregard my email. Obviously, 5 minutes is way too long.
Sounds like we need to do some performance tuning. For that we need to
create a test db with enough rows. I don't suppose your database is
good for public testing? Thanks. Mark
Sounds like we need to do some performance tuning. For that we need to
create a test db with enough rows. I don't suppose your database is
good for public testing? Thanks. Mark
Omar Ramos
Jun 27, 2011, 6:35:56 PM6/27/11
to joomla-...@googlegroups.com
I'd be interested in testing the dataset if the table could be made available for testing,
Zachariha Robichaud
Jun 27, 2011, 6:38:55 PM6/27/11
to joomla-...@googlegroups.com
If its taking along time to load on decent internet then its ether a permission error with the files or a db connection issue...
--
TurtleBytes.com
On Mon, Jun 27, 2011 at 3:25 PM, Mark Dexter <dexter...@gmail.com> wrote:
--
TurtleBytes.com
AKA
Za...@Turtlebytes.com
Thierry bela nanga
Jun 28, 2011, 4:34:26 AM6/28/11
to joomla-...@googlegroups.com
if you look at the log from mysql query slow I posted before you will see this:
# Query_time: 38
it means the query takes 38 seconds to complete, some of them take more than 5minutes, slowing down mysql. it is the only database on that server,
# Query_time: 38
it means the query takes 38 seconds to complete, some of them take more than 5minutes, slowing down mysql. it is the only database on that server,
Thierry bela nanga
Jun 28, 2011, 4:45:31 AM6/28/11
to joomla-...@googlegroups.com
in the meantime, I have disabled sorting on non indexes fields and "show All records" limit, I can use my back end again,
Thomas Kahl
Jun 28, 2011, 6:20:51 AM6/28/11
to Joomla! CMS Development
Hi,
why didn't you just add the table-fields to the index? An index is
always much faster... the only problem with an index is, that it takes
time to update (update, insert, delete). If you don't have lots of
updates per minute, it should not be a problem.
In MySQL messages like "Using temporary", "Using filesort" or "Copy to
temptable" show very (!!!) slow operations. The reasons can be
different - Index, not optimized queries, not enough (cache) memory
for the database...
Thomas
On Jun 28, 10:45 am, Thierry bela nanga <bna...@gmail.com> wrote:
> in the meantime, I have disabled sorting on non indexes fields and "show All
> records" limit, I can use my back end again,
>
> On Tue, Jun 28, 2011 at 9:34 AM, Thierry bela nanga <bna...@gmail.com>wrote:
>
>
>
>
>
>
>
>
>
> > if you look at the log from mysql query slow I posted before you will see
> > this:
>
> > # Query_time: 38
>
> > it means the query takes 38 seconds to complete, some of them take more
> > than 5minutes, slowing down mysql. it is the only database on that server,
>
> >>> >>> WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.nameEND AS
> >> Z...@Turtlebytes.com
why didn't you just add the table-fields to the index? An index is
always much faster... the only problem with an index is, that it takes
time to update (update, insert, delete). If you don't have lots of
updates per minute, it should not be a problem.
In MySQL messages like "Using temporary", "Using filesort" or "Copy to
temptable" show very (!!!) slow operations. The reasons can be
different - Index, not optimized queries, not enough (cache) memory
for the database...
Thomas
On Jun 28, 10:45 am, Thierry bela nanga <bna...@gmail.com> wrote:
> in the meantime, I have disabled sorting on non indexes fields and "show All
> records" limit, I can use my back end again,
>
> On Tue, Jun 28, 2011 at 9:34 AM, Thierry bela nanga <bna...@gmail.com>wrote:
>
>
>
>
>
>
>
>
>
> > if you look at the log from mysql query slow I posted before you will see
> > this:
>
> > # Query_time: 38
>
> > it means the query takes 38 seconds to complete, some of them take more
> > than 5minutes, slowing down mysql. it is the only database on that server,
>
> > On Mon, Jun 27, 2011 at 11:38 PM, Zachariha Robichaud <zach2...@gmail.com>wrote:
>
> >> If its taking along time to load on decent internet then its ether a
> >> permission error with the files or a db connection issue...
>
>
> >> If its taking along time to load on decent internet then its ether a
> >> permission error with the files or a db connection issue...
>
> >> On Mon, Jun 27, 2011 at 3:25 PM, Mark Dexter <dextercow...@gmail.com>wrote:
>
> >>> How long exactly does it take to load the page? In the administrative
> >>> back end, we aren't quite so worried about performance. Is the query
> >>> time in seconds? Thanks. Mark
>
> >>> On Mon, Jun 27, 2011 at 10:37 AM, Thierry bela nanga <bna...@gmail.com>
> >>> wrote:
> >>> > here you can see the Explain of each query, those queries come from the
> >>> back
> >>> > of com_content and should not use so many tables in the join to avoid
> >>> that
> >>> > situation
>
> >>> >http://dl.dropbox.com/u/1731360/explain1.htm
> >>> >http://dl.dropbox.com/u/1731360/explain2.htm
>
> >>> > On Mon, Jun 27, 2011 at 4:13 PM, Ofer Cohen <oferc...@gmail.com>
>
> >>> How long exactly does it take to load the page? In the administrative
> >>> back end, we aren't quite so worried about performance. Is the query
> >>> time in seconds? Thanks. Mark
>
> >>> On Mon, Jun 27, 2011 at 10:37 AM, Thierry bela nanga <bna...@gmail.com>
> >>> wrote:
> >>> > here you can see the Explain of each query, those queries come from the
> >>> back
> >>> > of com_content and should not use so many tables in the join to avoid
> >>> that
> >>> > situation
>
> >>> >http://dl.dropbox.com/u/1731360/explain1.htm
> >>> >http://dl.dropbox.com/u/1731360/explain2.htm
>
>
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "Joomla! CMS Development" group.
> >> To post to this group, send an email to joomla-...@googlegroups.com.
> >> To unsubscribe from this group, send email to
> >> joomla-dev-cm...@googlegroups.com.
> >> For more options, visit this group at
> >>http://groups.google.com/group/joomla-dev-cms?hl=en-GB.
>
> > --
> >http://tbela99.blogspot.com/
>
> > fax : (+33) 08 26 51 94 51
>
> --http://tbela99.blogspot.com/
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "Joomla! CMS Development" group.
> >> To post to this group, send an email to joomla-...@googlegroups.com.
> >> To unsubscribe from this group, send email to
> >> joomla-dev-cm...@googlegroups.com.
> >> For more options, visit this group at
> >>http://groups.google.com/group/joomla-dev-cms?hl=en-GB.
>
> > --
> >http://tbela99.blogspot.com/
>
> > fax : (+33) 08 26 51 94 51
>
Thierry bela nanga
Jun 28, 2011, 6:33:47 AM6/28/11
to joomla-...@googlegroups.com
The problem is from the query, we should avoid sql join as much as possible and avoid sorting on non indexes fields, that leads to poor performance on huge dataset,
Mark Dexter
Jun 28, 2011, 11:35:34 AM6/28/11
to joomla-...@googlegroups.com
It would very helpful if you could test the option of adding more
indexes. If that works, we could change the default table definition
to include these indexes. Thanks. Mark
indexes. If that works, we could change the default table definition
to include these indexes. Thanks. Mark
Thierry bela nanga
Jun 29, 2011, 8:35:51 AM6/29/11
to joomla-...@googlegroups.com
adding an index is very slow and make the table unusable during the process,
I have added an index to the title field and the query is much faster, but that does not really solve the problem,
what I suggest to speed up the queries:
- make a join only when necessary, lets take this query
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down,l.title AS language_title,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name
FROM jos_content AS a
LEFT JOIN `jos_languages` AS l ON l.lang_code = a.language
LEFT JOIN jos_users AS uc ON uc.id=a.checked_out
LEFT JOIN jos_viewlevels AS ag ON ag.id = a.access
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.title asc LIMIT 0, 20;
I can rewrite it this way
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down
FROM jos_content AS a
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.title asc LIMIT 0, 20
I have only twenty rows, for each row I make queries to jos_languages, jos_categories, etc.. to get the language, the author, the category name and son on,
it is much faster than the join on large dataset
I have added an index to the title field and the query is much faster, but that does not really solve the problem,
what I suggest to speed up the queries:
- make a join only when necessary, lets take this query
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down,l.title AS language_title,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name
FROM jos_content AS a
LEFT JOIN `jos_languages` AS l ON l.lang_code = a.language
LEFT JOIN jos_users AS uc ON uc.id=a.checked_out
LEFT JOIN jos_viewlevels AS ag ON ag.id = a.access
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.title asc LIMIT 0, 20;
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.title asc LIMIT 0, 20
it is much faster than the join on large dataset
Mark Dexter
Jun 29, 2011, 9:40:08 AM6/29/11
to joomla-...@googlegroups.com
Have you actually tried that? It doesn't seem obvious to me that
twenty queries for each joined table will be faster than one large
query. What if someone is showing 50 or 100 rows from the main table?
twenty queries for each joined table will be faster than one large
query. What if someone is showing 50 or 100 rows from the main table?
I don't understand your objection to indexes. You create the index
once and then it stays. If solves your issue that is much simpler and
better imo than messing with the queries. If indexing solves the
issue, we just need to make sure more indexes are created when we
initially create the database. Mark
Thierry bela nanga
Jun 29, 2011, 10:23:40 AM6/29/11
to joomla-...@googlegroups.com
doing this way you will never have queries that take five minutes to run, I prefer having 5 queries that take 0, 00012s each than having to run one big query
Thomas Kahl
Jun 29, 2011, 3:22:56 PM6/29/11
to Joomla! CMS Development
I would agree that splitting queries in an optimization process is a
good idea. (we usually do that with the Virtuemart queries to speed up
ecommerce sites). Query optimization should always be the first step.
But in this special case, i would also suggest to add indexes.
It will help and the number of queries doesn't go up for the 99.5% of
pages that don't have this problem ;-)
Thomas
> > > On Tue, Jun 28, 2011 at 4:35 PM, Mark Dexter <dextercow...@gmail.com>
> ...
>
> read more »
good idea. (we usually do that with the Virtuemart queries to speed up
ecommerce sites). Query optimization should always be the first step.
But in this special case, i would also suggest to add indexes.
It will help and the number of queries doesn't go up for the 99.5% of
pages that don't have this problem ;-)
Thomas
> > wrote:
>
> > >> It would very helpful if you could test the option of adding more
> > >> indexes. If that works, we could change the default table definition
> > >> to include these indexes. Thanks. Mark
>
> > >> On Tue, Jun 28, 2011 at 3:33 AM, Thierry bela nanga <bna...@gmail.com>
> > >> wrote:
> > >> > The problem is from the query, we should avoid sql join as much as
> > >> > possible
> > >> > and avoid sorting on non indexes fields, that leads to poor
> > performance
> > >> > on
> > >> > huge dataset,
>
> > >> > On Tue, Jun 28, 2011 at 11:20 AM, Thomas Kahl
> > >> > <joomlaexp...@googlemail.com>
>
> > >> It would very helpful if you could test the option of adding more
> > >> indexes. If that works, we could change the default table definition
> > >> to include these indexes. Thanks. Mark
>
> > >> On Tue, Jun 28, 2011 at 3:33 AM, Thierry bela nanga <bna...@gmail.com>
> > >> wrote:
> > >> > The problem is from the query, we should avoid sql join as much as
> > >> > possible
> > >> > and avoid sorting on non indexes fields, that leads to poor
> > performance
> > >> > on
> > >> > huge dataset,
>
> > >> > On Tue, Jun 28, 2011 at 11:20 AM, Thomas Kahl
>
> read more »
Thierry bela nanga
Jun 30, 2011, 5:30:48 AM6/30/11
to joomla-...@googlegroups.com
also I suggest we change the way the total number of records is calculated. I constantly see this in the log. to calculate the total it is clear to me that we don't need the order clause of the query,
# Query_time: 19.664000 Lock_time: 0.001000 Rows_sent: 62903 Rows_examined: 504834
SET timestamp=1309367219;
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down,l.title AS language_title,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name
FROM jos_content AS a
LEFT JOIN `jos_languages` AS l ON l.lang_code = a.language
LEFT JOIN jos_users AS uc ON uc.id=a.checked_out
LEFT JOIN jos_viewlevels AS ag ON ag.id = a.access
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.publish_up desc;
# Query_time: 19.664000 Lock_time: 0.001000 Rows_sent: 62903 Rows_examined: 504834
SET timestamp=1309367219;
SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down,l.title AS language_title,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name
FROM jos_content AS a
LEFT JOIN `jos_languages` AS l ON l.lang_code = a.language
LEFT JOIN jos_users AS uc ON uc.id=a.checked_out
LEFT JOIN jos_viewlevels AS ag ON ag.id = a.access
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
WHERE (a.state = 0 OR a.state = 1)
--
You received this message because you are subscribed to the Google Groups "Joomla! CMS Development" group.
To post to this group, send an email to joomla-...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-cm...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-cms?hl=en-GB.
Thierry bela nanga
Jun 30, 2011, 9:45:47 AM6/30/11
to joomla-...@googlegroups.com
I have found it is better to override JModelList::getTotal() in Articles List model so that I use a SELECT count() instead of fetching all rows of the content table
public function getTotal()
{
// Get a storage key.
$store = $this->getStoreId('getTotal');
// Try to load the data from internal storage.
if (!empty($this->cache[$store])) {
return $this->cache[$store];
}
// Load the total.
$query = clone($this->getListQuery());
$query->clear('order')->clear('select')->select('COUNT(a.id)');
$total = (int) $this->_db->setQuery($query)->loadResult();
// Check for a database error.
if ($this->_db->getErrorNum()) {
$this->setError($this->_db->getErrorMsg());
return false;
}
// Add the total to the internal cache.
$this->cache[$store] = $total;
return $this->cache[$store];
public function getTotal()
{
// Get a storage key.
$store = $this->getStoreId('getTotal');
// Try to load the data from internal storage.
if (!empty($this->cache[$store])) {
return $this->cache[$store];
}
// Load the total.
$query = clone($this->getListQuery());
$query->clear('order')->clear('select')->select('COUNT(a.id)');
$total = (int) $this->_db->setQuery($query)->loadResult();
// Check for a database error.
if ($this->_db->getErrorNum()) {
$this->setError($this->_db->getErrorMsg());
return false;
}
// Add the total to the internal cache.
$this->cache[$store] = $total;
return $this->cache[$store];
Mark Dexter
Jun 30, 2011, 11:12:30 AM6/30/11
to joomla-...@googlegroups.com
Would it be possible for you to create an issue in the Issue Tracker
and propose this as a patch? This is something I have wanted to do for
a long time but have not had time to do. Thanks. Mark
and propose this as a patch? This is something I have wanted to do for
a long time but have not had time to do. Thanks. Mark
Thierry bela nanga
Jun 30, 2011, 12:58:33 PM6/30/11
to joomla-...@googlegroups.com
the issue has beed added to the tracker
http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=26291
thanks all,
http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=26291
thanks all,
Reply all
Reply to author
Forward
0 new messages