Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2020-09-14 08:27:58

zero
Member
From: Lancashire
Registered: 2004-04-19
Posts: 1,349
Website

Reduce queries

I’m trying to reduce number of queries (and get an idea of how these work). I’ve noticed that these three take the longest time. Are they absolutely necessary? I don’t need an author name to be displayed anywhere on the site

2.15 | [SQL: SELECT name, val FROM txp_prefs WHERE user_name IN ('','peter') ORDER BY FIELD(user_name, '','peter') ]
1.17 | [SQL: SELECT name, version, load_order FROM txp_plugin WHERE status = 1 AND type IN (0,1,5) ORDER BY load_order ASC, name ASC ]
4.33 | [SQL: SELECT name, data FROM txp_lang WHERE lang = 'en' AND name != '' AND (event IN ('public','common') OR owner != '') ]

Wondrous Healing Wondrous ways to a healthy old age
Safe Reiki Harmless natural healing.
Gud One Blog

Offline

#2 2020-09-14 10:21:19

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,708
Website

Re: Reduce queries

The first is your personal prefs and site prefs, so kinda vital in order for Textpattern to know how to display to your site.

The second is for loading public plugins/libraries.

The third is the language strings for things like ‘category’ and date formats and so forth. This only gets language strings that are for public consumption but also (at present) plugin strings too. In time this might be able to come down a bit, but we’ve already optimised the strings as best we can right now.

So unfortunately these are all pretty important queries.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#3 2020-09-14 10:53:26

etc
Developer
Registered: 2010-11-11
Posts: 3,847
Website

Re: Reduce queries

Hi Peter,

nice to see you care about txp performance, though db queries account for about 1/5th of the runtime of a typical txp site.

The first query you mention retrieves site (and logged-in users) preferences, like language, date formats and so on. There is nothing we can do here.

The second one is loading active plugins. You can spare it by globally disabling plugins via a pref, but then, well, no plugins.

The third one loads language strings, and their number is dangerously increasing with each version. Each language contains more than 1000 strings, even if not all of them are used on the public side. I think at some stage we should split public and admin strings into separate tables, but bw compatibility-wise it’s not so trivial.

Offline

#4 2020-09-14 11:57:59

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,708
Website

Re: Reduce queries

etc wrote #325893:

Each language contains more than 1000 strings, even if not all of them are used on the public side.

Yeah. We only load public and common strings but the database still needs to trawl them to fetch just those. Is there anything we can do with a clever index to help?


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#5 2020-09-14 12:27:27

etc
Developer
Registered: 2010-11-11
Posts: 3,847
Website

Re: Reduce queries

Bloke wrote #325895:

Yeah. We only load public and common strings but the database still needs to trawl them to fetch just those. Is there anything we can do with a clever index to help?

Possibly lang_2=(lang, event), once we get rid of owner? I’ve read sort_union can be slow:

Using sort_union(lang_2,owner); Using where

Edit: not per se, but since it uses two indexes, the runtime seems to be roughly doubled. So we can hope to divide it by 2, to be compared with splitting tables.

Offline

#6 2020-09-14 12:43:30

zero
Member
From: Lancashire
Registered: 2004-04-19
Posts: 1,349
Website

Re: Reduce queries

Thanks guys, nice to know.


Wondrous Healing Wondrous ways to a healthy old age
Safe Reiki Harmless natural healing.
Gud One Blog

Offline

Board footer

Powered by FluxBB