MySQL Abuse

Mary · 2006-06-28 13:53:41

One per comment. Seems fair.

Uh, if that is the case (a query per comment), no that’s bad.

net-carver · 2006-06-28 13:57:55

Yeah,

I’d like to get that down to 34! Give me a day or two to think about the best way to cache the results of the comparisons the first time they are done.

M_i · 2006-06-28 14:05:24

Mary wrote:

Uh, if that is the case (a query per comment), no that’s bad.

This is what shows up in my debugging mode:

<txp:comments/> [SQL (0.000107): select Form from `txp_form` where `name` = ‘comments’ limit 1] [Form: comments] [SQL (0.000144): select *, unix_timestamp(posted) as time from `txp_discuss` where parentid=‘565’ and visible=1 order by posted asc] <txp:sed_get_comment_class hide_odd_even='1' /> [SQL (0.000097): SELECT name,RealName,email FROM txp_users WHERE name=’[authorsname]’ LIMIT 1] <txp:if_comments_preview> [<txp:if_comments_preview>: false] </txp:if_comments_preview> <txp:message/> <txp:comment_name/> <txp:comment_time/> <txp:comment_permlink> </txp:comment_permlink>

<br />
and then the part from <txp:sed_get_comment_class hide_odd_even='1' /> on is repeated for every comment.

Last edited by M_i (2006-06-28 14:06:03)

net-carver · 2006-06-28 14:08:04

Yep, just looked at the code, should be able to reduce it to one extra query for the whole comment list, ~~even without caching.~~

~~I put the code to pull the author details in the per-comment call instead of making it outside the per-comment call. Will fix.~~

Speaking too fast. More details when I have more brain cycles on it.

Last edited by net-carver (2006-06-28 14:09:51)

Mary · 2006-06-28 14:36:04

Steve: pull them into array, declare it as static and check for isset.

net-carver · 2006-06-28 14:48:39

Mary,

Thanks for that pointer. I haven’t used statics in PHP before so I’ll read up first just to check they are like C/C++. ~~If they are then that might be the way to go but for now I have reduced the query count a slightly different way so it’s only needed once per article.~~

~~I just made the variable a global and use isset: if it’s not initialised, make the SQL call and populate the variable, otherwise just go with the existing value.~~

~~Working a treat on my localhost.~~

~~Any blaring klaxon’s with that approach?~~

Last edited by net-carver (2006-06-28 16:28:44)

net-carver · 2006-06-28 16:27:29

Mary,

further to the last post. Yes, now I’ve had a little read on static I’ll be switching over to it, there is no need for the value to be global as it’s only used in the one routine.

Thank you muchly.

net-carver · 2006-06-28 16:42:26

All,

sorry about that. There is a caching version of the comment pack now available. It reduces the overhead to one query per page (if it has any comments at all).

maniqui · 2006-06-28 17:54:04

Well… urgent help needed :D (and first: excuse the “technical” language I use about mysql, queries, etc…)

After reading this thread, I decided to take a deeper look to the tag trace.
I was having +300 queries, but I didn’t care too much.

But, as I said, I decided to make a tag trace and I have found something that makes me fear.
I dont know how to explain this… let me see…

In all my pages, there are many many queries made by txp:bas_lightbox even when I’m not “using” the plug-in in the page template…

So, the thing I have found is that each time a query calls an article that has <txpbas_lightbox /> inside the body, that tag is procesed! (this is when those many many queries to the database are made) even when the viewed page isn’t using/displaying that plug-in
:(

Example:

A simple <txp:article_custom section="fotolog" form="permlink" limit="1" /> will generate all the following tag trace!! (the “permlink” form is just a <txp:permlink />).

[SQL (0,352279): select *, unix_timestamp(Posted) as uPosted from `textpattern` where 1 and Status='4' and Posted < now() and Section = 'fotolog' order by Posted desc limit 0, 1]

[SQL (0,000446): select Form from `txp_form` where `name` = 'permlink' limit 1]

[Formulario: permlink] [artículo 39] <txp:bas_lightbox wraptag="div" wrapclass="thumbs" img="121,120,119,118,117,116,115,114,109,80,113,112,111,110,107,106,104,90,89,92,88,93,83,82,81"/>

[SQL (0,037095): select * from `txp_image` where id='121' limit 1] [SQL (0,000191): select id, ext,caption from `txp_image` where id ="121"]

[SQL (0,000184): select * from `txp_image` where id='120' limit 1] [SQL (0,000141): select id, ext,caption from `txp_image` where id ="120"]

[SQL (0,000178): select * from `txp_image` where id='119' limit 1] [SQL (0,000162): select id, ext,caption from `txp_image` where id ="119"]

[SQL (0,000182): select * from `txp_image` where id='118' limit 1] [SQL (0,000146): select id, ext,caption from `txp_image` where id ="118"]

[SQL (0,000188): select * from `txp_image` where id='117' limit 1] [SQL (0,000142): select id, ext,caption from `txp_image` where id ="117"]

[SQL (0,000177): select * from `txp_image` where id='116' limit 1] [SQL (0,000142): select id, ext,caption from `txp_image` where id ="116"]

[SQL (0,000186): select * from `txp_image` where id='115' limit 1] [SQL (0,000143): select id, ext,caption from `txp_image` where id ="115"]

[SQL (0,000181): select * from `txp_image` where id='114' limit 1] [SQL (0,000139): select id, ext,caption from `txp_image` where id ="114"]

[SQL (0,000177): select * from `txp_image` where id='109' limit 1] [SQL (0,000139): select id, ext,caption from `txp_image` where id ="109"]

[SQL (0,004232): select * from `txp_image` where id='80' limit 1] [SQL (0,000147): select id, ext,caption from `txp_image` where id ="80"]

[SQL (0,000178): select * from `txp_image` where id='113' limit 1] [SQL (0,000142): select id, ext,caption from `txp_image` where id ="113"]

[SQL (0,000178): select * from `txp_image` where id='112' limit 1] [SQL (0,000141): select id, ext,caption from `txp_image` where id ="112"]

[SQL (0,000176): select * from `txp_image` where id='111' limit 1] [SQL (0,000146): select id, ext,caption from `txp_image` where id ="111"]

[SQL (0,000179): select * from `txp_image` where id='110' limit 1] [SQL (0,000137): select id, ext,caption from `txp_image` where id ="110"]

[SQL (0,000178): select * from `txp_image` where id='107' limit 1] [SQL (0,000142): select id, ext,caption from `txp_image` where id ="107"]

[SQL (0,000175): select * from `txp_image` where id='106' limit 1] [SQL (0,000137): select id, ext,caption from `txp_image` where id ="106"]

[SQL (0,000185): select * from `txp_image` where id='104' limit 1] [SQL (0,000155): select id, ext,caption from `txp_image` where id ="104"]

[SQL (0,000174): select * from `txp_image` where id='90' limit 1] [SQL (0,000137): select id, ext,caption from `txp_image` where id ="90"]

[SQL (0,000171): select * from `txp_image` where id='89' limit 1] [SQL (0,000138): select id, ext,caption from `txp_image` where id ="89"]

[SQL (0,000182): select * from `txp_image` where id='92' limit 1] [SQL (0,000146): select id, ext,caption from `txp_image` where id ="92"]

[SQL (0,000174): select * from `txp_image` where id='88' limit 1] [SQL (0,000139): select id, ext,caption from `txp_image` where id ="88"]

[SQL (0,000176): select * from `txp_image` where id='93' limit 1] [SQL (0,000145): select id, ext,caption from `txp_image` where id ="93"]

[SQL (0,000176): select * from `txp_image` where id='83' limit 1] [SQL (0,000138): select id, ext,caption from `txp_image` where id ="83"]

[SQL (0,000176): select * from `txp_image` where id='82' limit 1] [SQL (0,000141): select id, ext,caption from `txp_image` where id ="82"]

[SQL (0,000180): select * from `txp_image` where id='81' limit 1] [SQL (0,000143): select id, ext,caption from `txp_image` where id ="81"]

<txp:permlink/>
</code></pre>

Why are all this calls made?
I’m not even procesing/calling the “body” of the called article, I just need the “permlink”.

Of course, this happen also in generated articles-list where I only show the excerpt, but for some reason, all the queries to the bas_lightbox are also made! (why??!!) :(

That’s the reason why I’m having +300 queries in the homepage and even +70 in a simple contact page!

Last edited by maniqui (2006-06-28 17:57:30)

maniqui · 2006-06-28 18:18:55

Sorry for being the double post and the little off topic.

Here is a home page tag trace
Look all the queries made to the DB by bas_lightbox, even when I’m not using that plug-in in the home-page.

Thanks in advance.

Sencer · 2006-06-28 18:39:09

You need to talk with the author of the plugin, if you have plugin specific questions.

Textpattern has always fetched and processed the article-body, even in lists that don’t explicitly call the body-tag. Changes had been considered in the past but dropped because we were not sure if any plugins rely on that behaviour. If I am not mistaken Alex finall checked in the change (which will roll out in 4.0.4) after no indication was found that any plugins actually do rely on it. Unless there are complaints before the next release, it’ll probably stay in.

maniqui · 2006-06-28 18:52:02

Thanks, Sencer.
I have asked at the plug-in thread and wet gave me a similar answer.

I will have to rethink the use of that plug-in in the article bodies…

Textpattern CMS

Textpattern CMS support forum

#13 2006-06-28 13:53:41

Re: MySQL Abuse

#14 2006-06-28 13:57:55

Re: MySQL Abuse

#15 2006-06-28 14:05:24

Re: MySQL Abuse

#16 2006-06-28 14:08:04

Re: MySQL Abuse

#17 2006-06-28 14:36:04

Re: MySQL Abuse

#18 2006-06-28 14:48:39

Re: MySQL Abuse

#19 2006-06-28 16:27:29

Re: MySQL Abuse

#20 2006-06-28 16:42:26

Re: MySQL Abuse

#21 2006-06-28 17:54:04

Re: MySQL Abuse

#22 2006-06-28 18:18:55

Re: MySQL Abuse

#23 2006-06-28 18:39:09

Re: MySQL Abuse

#24 2006-06-28 18:52:02

Re: MySQL Abuse

Board footer