Textpattern CMS

#16 2014-10-10 00:53:35

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

I just have no clue about performance. I also can’t figure out – as it works right now, isn’t it showing the current book as another book by the author?

Thanks Michael
The current code it doesn’t find the current book as another book by author, which is why i think I went this route. I am trying your solution out to no success yet, trying to figure out why your code is failing (for me).

#17 2014-10-10 00:56:30

michaelkpate

Moderator

From: Avon Park, FL

Registered: 2004-02-24

Posts: 1,379

Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284670:

Thanks Michael
The current code it doesn’t find the current book as another book by author, which is why i think I went this route. I am trying your solution out to no success yet, trying to figure out why your code is failing (for me).

I might have missed something but I did test the variable function.

---

Michael K. Pate | Third Superpower

#18 2014-10-10 01:17:50

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

Hey Michael
I had to strikeout the “else if” part for now as it was failing completely (?) at that point. But this adjusted code spits out this (after the code) The very final “[<txp:if_custom_field name=“booksbythisauthor”>: false]” should be true, so a bit confused. And then there is the Tag error.

<txp:variable name="booksbythisauthor" value="" />

<txp:if_custom_field name="Author_1"> Line 1
  <txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  >
      <txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
  </txp:article_custom custom>

</txp:if_custom_field>

[repeat for Author_2 et al]

<txp:if_custom_field name="booksbythisauthor">
  <txp:article_custom form="book_title" id='<txp:variable name="booksbythisauthor"  />' section="Books" sort="custom_4 desc" limit="50" />
</txp:if_custom_field>


<!-- Ends other Books by this Author -->

</txp:if_individual_article>
<txp:variable name="booksbythisauthor" value="" />
<txp:if_custom_field name="Author_1">
	[<txp:if_custom_field name="Author_1">: true]
	<txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  >
		[attribute 'custom_1']
		<txp:custom_field name="Author_1" />
		[/attribute]
		Tag error:  <txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  > -> Textpattern Notice: Unknown tag attribute: custom_1  on line 758
		[SQL (0.00319290161133): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where 1=1 and Status = 4 and Posted <= now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') and Section IN ('Books') order by Posted desc limit 0, 10]
		[article 2236]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2235]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2234]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2233]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2232]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2231]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2230]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2228]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2227]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
		[article 2222]
		<txp:variable name="booksbythisauthor" value='<txp:variable name="booksbythisauthor"  /><txp:article_id />,' />
			[attribute 'value']
			<txp:variable name="booksbythisauthor"  />
			<txp:article_id />
			[/attribute]
	</txp:article_custom>
	<txp:if_custom_field name="booksbythisauthor">
		[<txp:if_custom_field name="booksbythisauthor">: false]
	</txp:if_custom_field>
</txp:if_custom_field>

Last edited by lazlo (2014-10-10 01:38:24)

#19 2014-10-10 01:26:35

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

Also wonder if a server gurus have any opinion on why my original code runs on my MAMP/Macbook at this speed:

<txp:soo_article_filter update_set="custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41)">
			[SQL (0.187816143036): create temporary table textpattern select * from textpattern]
			[SQL (0.634253025055): update textpattern set custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41) where 1=1]

compared to what my mighty host is providing:

<txp:soo_article_filter update_set="custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41)">
			[SQL (15.7792680264): create temporary table textpattern select * from textpattern]
			[SQL (14.9772799015): update textpattern set custom_44=concat_ws(';',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41) where 1=1]

Last edited by lazlo (2014-10-10 01:29:23)

#20 2014-10-10 03:00:17

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

Or for anymore clarity if anyone else is reading this far down 4 years ago this was my original question to John Jeff Soo:

lazlo wrote #226850:

Hey Jeff

Another question that maybe you can help with.

I have the following fields:
Author_1
Author_2
Author_3
Editor_1
Editor_2
etc.

I want to merge/join/union into a temporary table (you choice on most appropriate) and then match against an existing author_1.
I was doing this individually but came up against matching null fields and having to have a function for every field.

So what I was doing

Does Author_1 match
Author_1

Does Author_1 match
Author_2

Does Author_1 match
Author_3

or for actual code:

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Author_1=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” limit=“50” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Author_2=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Author_3=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Editor_1=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Editor_2=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Translator_1=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Translator_2=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Photographer=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

<txp:asy_wondertag>
<txp:article_custom form=“book_title” Illustrator=”<txp:custom_field name=“Author_1” />”
section=“Books” sort=“custom_4 desc” />
</txp:asy_wondertag>

The above code kinda works except I can’t get it to ignore NULL entries so after I saw your plug-in I thought it might be easier to merge all those fields and do one function instead.
This is one reference I found that has brought me to you with this question, as it seems to be what you are doing with you plugin.
http://www.webmasterworld.com/forum112/60.htm

regards
Les Smith

p.

Last edited by lazlo (2014-10-11 19:22:48)

#21 2014-10-10 03:15:19

michaelkpate

Moderator

From: Avon Park, FL

Registered: 2004-02-24

Posts: 1,379

Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284672:

Hey Michael
I had to strikeout the “else if” part for now as it was failing completely (?) at that point. But this adjusted code spits out this (after the code) The very final “[<txp:if_custom_field name=“booksbythisauthor”>: false]” should be true, so a bit confused. And then there is the Tag error.

Try putting the code somewhere you can see it on the page

Books by This Author: <txp:variable name="booksbythisauthor"  />

The final output should be something like “3,2,1,” – you can see a simpler version here at the bottom.

Tag error:  <txp:article_custom custom_1='<txp:custom_field name="Author_1" />' section="Books"  > -> Textpattern Notice: Unknown tag attribute: custom_1  on line 758

I think that means your custom field isn’t actually named “custom_1” – I think Jeff’s plugin used a different system of referring to them.

From article_custom

customfieldname="value"

* Restrict to articles with specified value for specified custom field name. Replace "customfieldname" with the name of the custom field.
* IMPORTANT: Using dashes (-) or spaces may cause errors or render this feature ineffectual. Underscores in both custom field names and values are confirmed to work.
* Default: unset.

---

Michael K. Pate | Third Superpower

#22 2014-10-10 08:13:58

ruud

Developer Emeritus

From: a galaxy far far away

Registered: 2006-06-04

Posts: 5,068

Website

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284674:

Also wonder if a server gurus have any opinion on why my original code runs on my MAMP/Macbook at this speed:

Probably because your Macbook has a blazing fast SSD and/or different MySQL settings, which helps when you create a temporary table, while a webhost typically has multiple users on the same server using a raid array of normal harddisks with MySQL set to conserve memory.

#23 2014-10-10 08:39:18

gaekwad

Server grease monkey

From: People's Republic of Cornwall

Registered: 2005-11-19

Posts: 4,802

GitHub

Re: Help cleaning up bulky/slow sql calls

+1 for the SSD explanation. I have a similar thing here when working on localhost Magento: on my spinning drive, it’s slow as heck (many files called per operation – really, it’s crazy), but on a Digital Ocean SSD-based droplet – even the $5 option – it’s way faster.

---

Textpattern CMS demo – tech updates

Code is Poetry; Text is Pattern.

#24 2014-10-10 09:02:10

etc

Developer

Registered: 2010-11-11

Posts: 5,689

Website GitHub

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284653:

Here is my existing textpattern code snippet and because I am not a PHP/MYSQL denizen could you explain your code a bit more in plain english or textpatternish.

Ah, you want to output the books having the current Author_1 (say, custom_1) or Editor_1 (custom_11) as contributors? This should work:

create an article-type etc_search query (say, id=1) with {"OR":";"} as logical operators, and

{custom_1,custom_11} AND Section='Books' AND ID != <txp:page_url type="id" /> ORDER BY custom_4 DESC

as query string. Then add this to the individual article form:

<txp:etc_search_results id="1"  form="book_title" limit="40"
	query='<txp:custom_field name="Author_1" />;<txp:custom_field name="Author_2" />;<txp:custom_field name="Editor_1" />;<txp:custom_field name="another_contributor_field" />;...' />

It will internally transform it into

... WHERE ( (custom_1 LIKE '%Author_1%' OR custom_11 LIKE '%Author_1%')
OR (custom_1 LIKE '%Editor_1%' OR custom_11 LIKE '%Editor_1%') ... )
AND Section='Books' AND ID != current_ID ORDER BY custom_4 DESC LIMIT 40

and ignore empty contributor fields.

---

bloatware

#25 2014-10-10 09:21:00

etc

Developer

Registered: 2010-11-11

Posts: 5,689

Website GitHub

Re: Help cleaning up bulky/slow sql calls

Another option, if you privilege Author_1 first: create a query with empty ({}) logic and the following search string

{CONCAT(';', CONCAT_WS(';', custom_1,custom_2,custom_3), ';') LIKE '%;$0;%'} AND Section='Books' AND ID != <txp:page_url type="id" /> ORDER BY custom_4 DESC

and call

<txp:if_custom_field name="Author_1">
	<txp:etc_search_results id="1"  form="book_title" limit="40"
		query='<txp:custom_field name="Author_1" />' />
<txp:else />
...

That’s more or less what you were doing before, but without cloning.

Last edited by etc (2014-10-10 15:04:47)

---

bloatware

#26 2014-10-10 12:40:15

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,319

Re: Help cleaning up bulky/slow sql calls

etc wrote #284694:

current_ID

Pardon for asking a little OT: what is that current_ID? Is current_ a prefix-like language entity that one can combine with any column name to get info on a specific field of the query’s own table row?

Last edited by uli (2014-10-10 12:43:55)

---

_{In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links}

#27 2014-10-10 14:32:26

etc

Developer

Registered: 2010-11-11

Posts: 5,689

Website GitHub

Re: Help cleaning up bulky/slow sql calls

uli wrote #284696:

what is that current_ID? Is current_ a prefix-like language entity that one can combine with any column name to get info on a specific field of the query’s own table row?

No, pardon my loose notations, it’s only the current article ID as output by <txp:page_url type="id" /> in the query above, to exclude the current article from the search results.

---

bloatware

#28 2014-10-10 15:12:44

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,319

Re: Help cleaning up bulky/slow sql calls

Ah, and I was already dreaming away … what I could do shortly … 8))

---

_{In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links}

#29 2014-10-11 19:26:01

lazlo

Member

Registered: 2004-02-24

Posts: 110

Re: Help cleaning up bulky/slow sql calls

gaekwad wrote #284693:

+1 for the SSD explanation. I have a similar thing here when working on localhost Magento: on my spinning drive, it’s slow as heck (many files called per operation – really, it’s crazy), but on a Digital Ocean SSD-based droplet – even the $5 option – it’s way faster.

I wish I had an SSD, this code has been “acceptable” one might say even fast for 4 years until last week and now dead dog slow.
Thank you everyone for the code help.

#30 2014-10-12 13:11:12

gaekwad

Server grease monkey

From: People's Republic of Cornwall

Registered: 2005-11-19

Posts: 4,802

GitHub

Re: Help cleaning up bulky/slow sql calls

lazlo wrote #284734:

I wish I had an SSD […]

If you’re interested, there was a healthy discussion about Digital Ocean (SSD) hosting here: forum.textpattern.com/viewtopic.php?id=40928

---

Textpattern CMS demo – tech updates

Code is Poetry; Text is Pattern.