Go to main content

Navigation menu

Textpattern CMS support forum

You are not logged in. Register | Login | Help

  1. Index
  2. » How do I…?
  3. » Help cleaning up bulky/slow sql calls

Pages: 1 2 3

#1 2014-10-08 22:16:55

lazlo
Member
Registered: 2004-02-24
Posts: 110

Help cleaning up bulky/slow sql calls

I have been using the same code for oh so many years now we seem to be having a problem with the speed of the mysql calls. Next week I about to launch into a site rewrite and I am looking for feedback. I think I need to move hosts because the response is so slow and obviously my code is bulky. In particular I am looking for a new solution for this slow code:

This code searches for book contributions by the same author. All those custom fields are different types of contributer (Author, Editor, Photographer, etc)

<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:if_custom_field name="Author_1">
		[<txp:if_custom_field name="Author_1">: true]
		<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]
			<txp:article_custom form="book_title" Soo_Plugin_Concat_Contributors='%<txp:custom_field name="Author_1" />%' section="Books" sort="custom_4 desc" limit="50" />
				[attribute 'Soo_Plugin_Concat_Contributors']
				<txp:custom_field name="Author_1" />
				[/attribute]
				[SQL (0.0123620033264): 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') and custom_44 like '%Morris Panych%'  order by custom_4 desc limit 0, 50] p.

Any feedback on the part above or the whole thing would be appreciated.

regards
Les Smith




Complete runtime feedback below:
<!-- Runtime:    32.062 -->
<!-- Query time: 31.948930 -->
<!-- Queries: 58 -->
<!-- Memory: 7204Kb, <txp:category2 title="y"/> -->
<!-- txp tag trace: 
[SQL (0.000147819519043): select name, data from txp_lang where lang='en-gb' AND ( event='public' OR event='common')]
[SQL (0.000815868377686): select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order]
[SQL (0.000278949737549): select name, val from txp_prefs where prefs_id=1 AND user_name='']
[SQL (0.000118970870972): 
    SELECT
      `name` AS custom_set,
      `val` AS name,
      `position`,
      `html` AS type
    FROM
      `txp_prefs`
    WHERE
      `event`='custom'
    ORDER BY
      `position`
  ]
[SQL (5.00679016113E-5): 
      SELECT
        `name`, `val`
      FROM
        `txp_prefs`
      WHERE
        `html` = 'date-picker'
      AND
        `name` LIKE 'custom_%'
      ORDER BY
        `name`
    ]
[SQL (4.10079956055E-5): 
      SELECT
        `name`, `val`
      FROM
        `txp_prefs`
      WHERE
        `html` = 'time-picker'
      AND
        `name` LIKE 'custom_%'
      ORDER BY
        `name`
    ]
[SQL (0.00347089767456): select ID,Section from textpattern where url_title like '7-stories' AND Section='books' and Status >= 4 limit 1]
[SQL (5.19752502441E-5): select name from txp_section where `name` like 'books' limit 1]
[SQL (4.10079956055E-5): select name from txp_category where `name` like '7-stories' limit 1]
[SQL (3.81469726562E-5): select name from txp_category where `name` like '7-stories' limit 1]
[SQL (4.2200088501E-5): select name from txp_section where `name` like 'books' limit 1]
[SQL (4.60147857666E-5): select page, css from txp_section where name = 'books' limit 1]
[SQL (0.000432968139648): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where ID=1229 and Status in (4,5)]
[article 1229]
[SQL (0.00121307373047): select ID, Title, url_title, unix_timestamp(Posted) as uposted
			from textpattern where Posted > '2010-03-26 21:10:47' and Section = 'books' and Status=4 and Posted < now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') order by Posted asc limit 1]
[SQL (0.00125908851624): select ID, Title, url_title, unix_timestamp(Posted) as uposted
			from textpattern where Posted < '2010-03-26 21:10:47' and Section = 'books' and Status=4 and Posted < now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') order by Posted desc limit 1]
[SQL (0.000297069549561): select host from txp_log where ip='24.84.245.239' limit 1]
[SQL (0.000230073928833): insert into txp_log set `time`=now(),page='/books/7-stories',ip='24.84.245.239',host='S0106602ad0963d39.vc.shawcable.net',refer='',status='200',method='GET']
[SQL (5.79357147217E-5): select user_html from txp_page where name='bookpage']
[Page: bookpage]
<txp:if_article_list>
	[<txp:if_article_list>: false]
</txp:if_article_list>
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:title />
	<txp:section title="1" />
		[SQL (4.91142272949E-5): select title from txp_section where name='books']
	<txp:site_name />
</txp:if_individual_article>
<txp:output_form form="google_analytics" />
	[SQL (4.60147857666E-5): select Form from txp_form where name='google_analytics']
	[Form: google_analytics]
<txp:link_to_home />
<txp:section />
<txp:section title="1"/>
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:chh_if_data>
		[<txp:chh_if_data>: true]
		<txp:category2 />
		<txp:category2 title="1"/>
			[SQL (4.6968460083E-5): select title from txp_category where name='Drama' and type='article']
	</txp:chh_if_data>
</txp:if_individual_article>
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
</txp:if_individual_article>
<txp:output_form form="newSearch" />
	[SQL (4.79221343994E-5): select Form from txp_form where name='newSearch']
	[Form: newSearch]
	<txp:output_form form="search-input" />
		[SQL (4.41074371338E-5): select Form from txp_form where name='search-input']
		[Form: search-input]
	<txp:hide>
	</txp:hide>
	<txp:section_list break="li" exclude="search,tag,XML_Reader,Kudos"  />
		[SQL (5.19752502441E-5): select name, title from txp_section where name != 'default' and name not in('search','tag','XML_Reader','Kudos') order by name ASC]
	<txp:category_list break="li" wraptag="ul" exclude="Frontlist,Backlist" />
		[SQL (5.00679016113E-5): select name, title from txp_category where type = 'article' and name not in('default','root') and name not in('Frontlist','Backlist')  order by name ASC]
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:article form="paypal_form" />
		[SQL (5.00679016113E-5): select Form from txp_form where name='paypal_form']
		[Form: paypal_form]
		<txp:title />
		<txp:custom_field name="Can_Price" />
		<txp:Title />
		<txp:custom_field name="ISBN_13" />
		<txp:custom_field name='Can_Price' />
		<txp:custom_field name="US_Price" />
		<txp:Title />
		<txp:custom_field name="ISBN_13" />
		<txp:custom_field name='US_Price' />
		<txp:if_custom_field name="EPUB_Price">
			[<txp:if_custom_field name="EPUB_Price">: false]
		</txp:if_custom_field>
</txp:if_individual_article>
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:article form="individual_book_form" />
		[SQL (6.19888305664E-5): select Form from txp_form where name='individual_book_form']
		[Form: individual_book_form]
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:upm_textile>
				<txp:custom_field name="Sales_Copy" />
			</txp:upm_textile>
		</txp:chh_if_data>
		<txp:article_url_title />
		<txp:article_image class="frontListImage" />
		<txp:posted />
		<txp:section />
		<txp:section title="y"/>
		<txp:permlink>
			<txp:title />
			<txp:chh_if_data>
				[<txp:chh_if_data>: true]
				<txp:custom_field name="Subtitle" />
				[<txp:chh_if_data>: false]
			</txp:chh_if_data>
		</txp:permlink>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Author_1URL" />
			<txp:custom_field name="Author_1" />
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Author_2URL" />
			<txp:custom_field name="Author_2" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Author_3URL" />
			<txp:custom_field name="Author_3" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Editor_1URL" />
			<txp:custom_field name="Editor_1" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Editor_2URL" />
			<txp:custom_field name="Editor_2" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Translator_1URL" />
			<txp:custom_field name="Translator_1" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Translator_2URL" />
			<txp:custom_field name="Translator_2" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Photographer_URL" />
			<txp:custom_field name="Photographer" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Illustrator_URL" />
			<txp:custom_field name="Illustrator" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Introduction" />
			<txp:custom_field name="Introduction" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:body />
		<txp:site_url />
		<txp:custom_field name="ISBN_13" />
		<txp:custom_field name="ISBN-10" />
		<txp:custom_field name="Width-In" />
		<txp:custom_field name="Height-In" />
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Depth" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:custom_field name="Page_Count" />
		<txp:custom_field name="Can_Price" />
		<txp:custom_field name="US_Price" />
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="Rights" />
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:category1 title="1" />
			[SQL (5.91278076172E-5): select title from txp_category where name='Backlist' and type='article']
		<txp:category2 title="1" />
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="bisac-subj" />
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="EditionNumber" />
			<txp:custom_field name="Binding" />
		</txp:chh_if_data>
		<txp:chh_if_data>
			[<txp:chh_if_data>: true]
			<txp:custom_field name="EditionStatement"/>
			[<txp:chh_if_data>: false]
		</txp:chh_if_data>
		<txp:site_url />
</txp:if_individual_article>
<txp:if_article_list>
	[<txp:if_article_list>: false]
</txp:if_article_list>
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:if_custom_field name="quotes">
		[<txp:if_custom_field name="quotes">: true]
		<txp:upm_textile>
			<txp:custom_field name="quotes"/>
		</txp:upm_textile>
	</txp:if_custom_field>
	<txp:if_custom_field name="awards">
		[<txp:if_custom_field name="awards">: true]
		<txp:upm_textile>
			<txp:custom_field name="awards"/>
		</txp:upm_textile>
	</txp:if_custom_field>
</txp:if_individual_article>
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Author_1" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Author_1" />
		[/attribute]
		[SQL (0.0476641654968): 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 ('Authors') and custom_1 like 'Morris Panych'  order by Posted desc limit 0, 1]
		[article 1733]
		[SQL (6.00814819336E-5): select Form from txp_form where name='author_bio_excerpt']
		[Form: author_bio_excerpt]
		<txp:permlink>
			<txp:title />
		</txp:permlink>
		<txp:excerpt />
		<txp:permlink />
		<txp:title />
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Author_2" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Author_2" />
		[/attribute]
		[SQL (0.0608689785004): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Author_3" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Author_3" />
		[/attribute]
		[SQL (0.0608310699463): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Editor_1" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Editor_1" />
		[/attribute]
		[SQL (0.0608401298523): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Editor_2" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Editor_2" />
		[/attribute]
		[SQL (0.0608448982239): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Translator_1" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Translator_1" />
		[/attribute]
		[SQL (0.0608999729156): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Translator_2" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Translator_2" />
		[/attribute]
		[SQL (0.0609991550446): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Illustrator" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Illustrator" />
		[/attribute]
		[SQL (0.0609209537506): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
	<txp:article_custom section='Authors' form='author_bio_excerpt' Author_1='<txp:custom_field name="Photographer" />'  limit='1' />
		[attribute 'Author_1']
		<txp:custom_field name="Photographer" />
		[/attribute]
		[SQL (0.0609929561615): 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 ('Authors') and custom_1 like ''  order by Posted desc limit 0, 1]
</txp:if_individual_article>
<txp:if_individual_article>
	[<txp:if_individual_article>: true]
	<txp:if_custom_field name="Author_1">
		[<txp:if_custom_field name="Author_1">: true]
		<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]
			<txp:article_custom form="book_title" Soo_Plugin_Concat_Contributors='%<txp:custom_field name="Author_1" />%' section="Books" sort="custom_4 desc" limit="50" />
				[attribute 'Soo_Plugin_Concat_Contributors']
				<txp:custom_field name="Author_1" />
				[/attribute]
				[SQL (0.0123620033264): 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') and custom_44 like '%Morris Panych%'  order by custom_4 desc limit 0, 50]
				[article 2946]
				[SQL (6.79492950439E-5): select Form from txp_form where name='book_title']
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 2732]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 2234]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1540]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1536]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1500]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1476]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1442]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1399]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1365]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1317]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1275]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1268]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
				[article 1229]
				[Form: book_title]
				<txp:permlink>
					<txp:custom_field name="ISBN_13" />
					<txp:title  />
				</txp:permlink>
				<txp:if_custom_field name="Author_1">
					[<txp:if_custom_field name="Author_1">: true]
					<txp:custom_field name="Author_1URL" />
					<txp:custom_field name="Author_1" />
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_2">
					[<txp:if_custom_field name="Author_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Author_3">
					[<txp:if_custom_field name="Author_3">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_1">
					[<txp:if_custom_field name="Editor_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Editor_2">
					[<txp:if_custom_field name="Editor_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_1">
					[<txp:if_custom_field name="Translator_1">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Translator_2">
					[<txp:if_custom_field name="Translator_2">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Photographer">
					[<txp:if_custom_field name="Photographer">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Illustrator">
					[<txp:if_custom_field name="Illustrator">: false]
				</txp:if_custom_field>
				<txp:if_custom_field name="Introduction">
					[<txp:if_custom_field name="Introduction">: false]
				</txp:if_custom_field>
				<txp:category2 />
				<txp:category2 title="y"/>
			[SQL (0.634590864182): drop temporary table textpattern]
		</txp:soo_article_filter>
	</txp:if_custom_field>
</txp:if_individual_article>
<txp:php>
</txp:php>
[ ~~~ secondpass ~~~ ]
<txp:upm_image image_name="9780889227026sm.jpg"  class="frontListImage" />
	[SQL (8.29696655273E-5): select * from txp_image where name = '9780889227026sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889226920sm.jpg"  class="frontListImage" />
	[SQL (5.10215759277E-5): select * from txp_image where name = '9780889226920sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889226647sm.jpg"  class="frontListImage" />
	[SQL (4.98294830322E-5): select * from txp_image where name = '9780889226647sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889226289sm.jpg"  class="frontListImage" />
	[SQL (5.10215759277E-5): select * from txp_image where name = '9780889226289sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889226241sm.jpg"  class="frontListImage" />
	[SQL (5.19752502441E-5): select * from txp_image where name = '9780889226241sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889225848sm.jpg"  class="frontListImage" />
	[SQL (5.00679016113E-5): select * from txp_image where name = '9780889225848sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889225602sm.jpg"  class="frontListImage" />
	[SQL (4.98294830322E-5): select * from txp_image where name = '9780889225602sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889225244sm.jpg"  class="frontListImage" />
	[SQL (5.19752502441E-5): select * from txp_image where name = '9780889225244sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889224810sm.jpg"  class="frontListImage" />
	[SQL (4.91142272949E-5): select * from txp_image where name = '9780889224810sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889224445sm.jpg"  class="frontListImage" />
	[SQL (5.50746917725E-5): select * from txp_image where name = '9780889224445sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889223929sm.jpg"  class="frontListImage" />
	[SQL (5.19752502441E-5): select * from txp_image where name = '9780889223929sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889223462sm.jpg"  class="frontListImage" />
	[SQL (4.98294830322E-5): select * from txp_image where name = '9780889223462sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889223349sm.jpg"  class="frontListImage" />
	[SQL (5.19752502441E-5): select * from txp_image where name = '9780889223349sm.jpg' limit 0, 1]
<txp:upm_image image_name="9780889222816sm.jpg"  class="frontListImage" />
	[SQL (5.29289245605E-5): select * from txp_image where name = '9780889222816sm.jpg' limit 0, 1]
p.

Offline

#2 2014-10-09 07:13:33

etc
Developer
Registered: 2010-11-11
Posts: 5,053
Website GitHub

Re: Help cleaning up bulky/slow sql calls

It says it all:

<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]
	...

As Jeff states on plugins page, performance may be an issue with soo_article_filter.

bloatware

Offline

#3 2014-10-09 09:15:59

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

Two options to improve this:

1. Cache the block containing the resource consuming SQL statements using a plugin like aks_cache
2. Don’t run that query on each page load, but when you change an article. This requires you to write a plugin that does this on the admin side when saving an article.

Offline

#4 2014-10-09 15:16:24

kuopassa
Plugin Author
From: Porvoo, Finland
Registered: 2008-12-03
Posts: 229
Website

Re: Help cleaning up bulky/slow sql calls

I too think caching could be quite helpful in that case. Maybe the code could be modified to create static files which then could be imported to where they’re meant to be actually used. Perhaps something with rah_external_output…

Textpattern Plugins

Offline

#5 2014-10-09 15:33:34

etc
Developer
Registered: 2010-11-11
Posts: 5,053
Website GitHub

Re: Help cleaning up bulky/slow sql calls

I just don’t see the point of using soo_article_filter here since nothing is filtered. You seem to clone the entire textpattern table just to glue some custom fields together, why? There should be much simpler way to achieve what you want (but what?)…

bloatware

Offline

#6 2014-10-09 15:49:27

michaelkpate
Moderator
From: Avon Park, FL
Registered: 2004-02-24
Posts: 1,379
Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

I looked the code over to offer a suggestion but I couldn’t figure out what it was supposed to be doing.

Michael K. Pate | Third Superpower

Offline

#7 2014-10-09 17:27:18

lazlo
Member
Registered: 2004-02-24
Posts: 110

Re: Help cleaning up bulky/slow sql calls

In plain english the call is doing this:
The page displays a book with is a list of contributors. There could be up to 10 contributors that exist in 10 different custom fields. I now want to search the database for any of their contributions to other books.
So if not null Author_1 has to searched against 10 fields, Author_2 has to be searched against 10 fields, Author_3 has to searched against 10 fields, etc until Author_10 is searched against 10 fields. EDIT: It only searches two fields in the current record against 10 fields in the other “book” records in the database. There are about 600 book pages to search.

Seperately I am looking for a caching tutorial because yes that could help, my pages are essentially static, the whole site could get recached after individual section/category updates because at most book pages, author pages are rarely updated. I am not sure how to cache or how to trigger. If someone with caching chops wants to earn a bit of dosh, send me a tweet @typesmith

Ha, on a separate note I registered here a day before Michael. :-) mu indeed.

Last edited by lazlo (2014-10-09 21:23:38)

Offline

#8 2014-10-09 20:44:27

etc
Developer
Registered: 2010-11-11
Posts: 5,053
Website GitHub

Re: Help cleaning up bulky/slow sql calls

I would try etc_search:

create an article-type etc_search query (say, id=1)

{custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41} AND section="Books" sort="custom_4 desc"

and add this to the individual article form:

<txp:variable name="contributors"><txp:php>
global $thisarticle;
echo trim(preg_replace('/,+/', ',', getThing("SELECT concat_ws(',',custom_1,custom_11,custom_14,custom_15,custom_16,custom_17,custom_18,custom_39,custom_41) FROM textpattern WHERE ID=".$thisarticle['thisid'])), ',');
</txp:php></txp:variable>

<txp:etc_search_results id="1" query='<txp:variable name="contributors" />' />

Edit: it was etc_search_results, not etc_search, sorry.

Last edited by etc (2014-10-10 08:03:28)

bloatware

Offline

#9 2014-10-09 21:09:29

lazlo
Member
Registered: 2004-02-24
Posts: 110

Re: Help cleaning up bulky/slow sql calls

Thank you etc

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. 

<!-- Other Books by this Author -->
<!-- Check to see if the author field is empty -->

<txp:if_custom_field name="Author_1">
<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)">
<txp:article_custom form="book_title" Soo_Plugin_Concat_Contributors='%<txp:custom_field name="Author_1" />%' section="Books" sort="custom_4 desc" limit="50" />
</txp:soo_article_filter>

<!-- Else use the Editor Feild -->
<txp:else />
<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)">
<txp:article_custom form="book_title" Soo_Plugin_Concat_Contributors='%<txp:custom_field name="Editor_1" />%' section="Books" sort="custom_4 desc" limit="50" />
</txp:soo_article_filter>
</txp:if_custom_field>


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

Last edited by lazlo (2014-10-09 21:09:47)

Offline

#10 2014-10-09 21:20:15

michaelkpate
Moderator
From: Avon Park, FL
Registered: 2004-02-24
Posts: 1,379
Website GitHub Mastodon

Re: Help cleaning up bulky/slow sql calls

DLGuin01 wrote #284651:

I’m neither a designer nor programmer but I’d like to get acquainted with TextPattern as a CMS system. In light of this I have it “downloaded” and I see all of the files, but I don’t know how to “open” it so I can establish my Blog. I read that you have a superior forum community. I appear to be finding out if that is indeed correct. Thanks for any help you can give me.

DLGuin01,

It is usually better to start a new forum thread to ask a queestion than to post in an existing one.

You can read the instructions for Installing over FTP if that is the method you wish to use.

If not, we can figure something else out.

Michael

Michael K. Pate | Third Superpower

Offline

#11 2014-10-09 21:27:39

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,304

Re: Help cleaning up bulky/slow sql calls

Hi DLGuin01, and welcome to the Textpattern forum.

Michael has already pointed you to the webserver installation. I just want to add that you can of course run Textpattern locally on MAMP/XAMP/WAMP if you want to play around a little, or you don’t have webspace yet or any content to show to the world.

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

Offline

#12 2014-10-09 21:28:12

lazlo
Member
Registered: 2004-02-24
Posts: 110

Re: Help cleaning up bulky/slow sql calls

Thanks Michael, you are a good man.

Offline

Pages: 1 2 3

  1. Index
  2. » How do I…?
  3. » Help cleaning up bulky/slow sql calls

Board footer

Powered by FluxBB