Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Pages: 1
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 != '') ]Offline
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
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
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
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 whereEdit: 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
Offline
Pages: 1