MySQL Abuse

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…

M_i · 2006-06-28 19:07:16

Sencer wrote:

Textpattern has always fetched and processed the article-body, even in lists that don’t explicitly call the body-tag.

What does that mean, exactly?
Would that mean that if you produce a list of articles that have a whole lot of text in them, that would take a whole lot longer than producing a list of articles with only a little bit of text in them, even if all you’re displaying in the list is the article’s title?

On your recommendation, I went looking for any queries that take a lot longer than the others, and the only ones that really stood out were those called by txp:article (or (chh_)article_custom) to produce lists.
Could that be because the some of my most recent articles happen to have a fair amount of text in them?

Sencer · 2006-06-28 19:58:55

Would that mean that if you produce a list of articles that have a whole lot of text in them, that would take a whole lot longer than producing a list of articles with only a little bit of text in them, even if all you’re displaying in the list is the article’s title?

In almost all cases: No – because Textpattern stores the posts with textile processed. Meaning there will be no measurable difference. Only txp-tags are stored as-is and processed on view, which usually doesn’t have any measurable negative effect either – the exception being plugin-calls that do db-queries. But like I said, this will probably change in 4.0.4 anyhow…

net-carver · 2006-06-29 03:35:59

Sencer wrote:

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.

Sencer, that behaviour is currently needed by some localisation plugins, but it could be worked around. Graeme’s recently posted plugin and one I am working on locally need to be able to localise articles for display in multi-lingual sites and both rely on the article body being parsed, even if the body content isn’t displayed. This is needed for switching the article title to the correct language, important in article lists, lists of links etc, where the body isn’t needed.

Thinking aloud about options to get around this…

Would it be possible to make the parsing of article data an admin-configurable option so that those, relatively few plugins could keep the behaviour?
Add a new event, at the end of populating the article data (even if the body and excerpt are unparsed), that plugins could register a handler for, that would allow them to switch the title etc, according to the language settings, before the title is used?
We re-evaluate implementation strategies for multi-lingual plugins in light of this.

Thanks for the warning about this change, hope we can work something out.

zem · 2006-06-29 04:33:18

A simpler solution: plugins that require a parsed article body can simply parse it.

Whatever the solution, the body parse changes are available for all to test and debug.

net-carver · 2006-06-29 08:17:50

Alex,

Will take a look over the next couple of days and post back to your referenced thread.

Thanks for the pointer.

Edit: Some feedback posted here.

Last edited by net-carver (2006-06-29 12:20:59)

graeme · 2006-06-29 16:31:06

net-carver,

zem wrote:

A simpler solution: plugins that require a parsed article body can simply parse it.

For your information this is why my plugin didn’t break after r1303 – a simple return parse($thing);

Textpattern CMS

Textpattern CMS support forum

#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

#25 2006-06-28 19:07:16

Re: MySQL Abuse

#26 2006-06-28 19:58:55

Re: MySQL Abuse

#27 2006-06-29 03:35:59

Re: MySQL Abuse

#28 2006-06-29 04:33:18

Re: MySQL Abuse

#29 2006-06-29 08:17:50

Re: MySQL Abuse

#30 2006-06-29 16:31:06

Re: MySQL Abuse

Board footer