performance issue with multilingual plugin

Creating and modifying plugins.
Post Reply
peggylon
Regular
Posts: 51
Joined: Tue Jul 01, 2008 6:32 pm
Location: Berlin
Contact:

performance issue with multilingual plugin

Post by peggylon » Sun Nov 20, 2011 12:58 pm

Yesterday my provider prompted me to optimize mysql queries and / or DB indizes, since the following request is way to time consuming (18 seconds). Due to the many joins it searches in 3.000.000 entries for one single entry.

I was shocked, since I'm quite convinced of the serendipity system. I figured, it could be a plugin issue, since the query in question seems to be in connection with the multilanguage-plugin.

Code: Select all

# Time: 111118 15:38:36
# User@Host: xyz
# Query_time: 12  Lock_time: 0  Rows_sent: 1  Rows_examined: 3034062
use db_xxx;
SELECT
                    multilingual_body.value AS multilingual_body,
multilingual_extended.value AS multilingual_extended,
multilingual_title.value AS multilingual_title,


                    e.id,
                    e.title,
                    e.timestamp,
                    e.comments,
                    e.exflag,
                    e.authorid,
                    e.trackbacks,
                    e.isdraft,
                    e.allow_comments,
                    e.last_modified,

                    a.realname AS author,
                    a.username AS loginname,
                    a.email


                FROM
                    multikuli_entries AS e
                    LEFT JOIN multikuli_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN multikuli_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN multikuli_category c
                        ON ec.categoryid = c.categoryid

LEFT OUTER JOIN multikuli_entryproperties multilingual_body
                                                 ON (e.id = multilingual_body.entryid AND multilingual_body.property = 'multilingual_body_en')
LEFT OUTER JOIN multikuli_entryproperties multilingual_extended
                                                 ON (e.id = multilingual_extended.entryid AND multilingual_extended.property = 'multilingual_extended_en')
LEFT OUTER JOIN multikuli_entryproperties multilingual_title
                                                 ON (e.id = multilingual_title.entryid AND multilingual_title.property = 'multilingual_title_en')
                    WHERE isdraft = 'false' AND e.timestamp <= 1321627200
                     GROUP BY e.id
            ORDER BY last_modified DESC
                      LIMIT 1;
Does it make sense to check (and if necessary modify) database indizes? Will they not be overwritten next time the plugin gets updated?

Actually I only have very few articles (about 10 out of 300), which needed translation. Is there a way to use the multilingual plugin (and execute the joined query) only when needed? Should I use static pages for those few english pages instead of employing the multilingual plugin?

What else coul I do to optimize my blogs performance and to content the provider (besides using the cache plugin which I just learned about while searching the forum)?

The blog link is: http://www.multikulinarisch.es

[added]
I took some time to further explore the entries with property 'multilingual_body_en' in table_entryproperties.
I got a list of 36 such entries in the database. That as wird,since there were onl 3 english articles. I found out, that the auto_save plugin saved lots of entries with the property 'multilingual_body_en'.

I deactivated auto_save plugin and deleted the [autosaved] entries. I guess that still doesn't solve the speed problem, though...


Thanks for your time!
----------
peggylon aka multikulinaria http://www.multikulinarisch.es

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: performance issue with multilingual plugin

Post by garvinhicking » Sun Nov 20, 2011 9:48 pm

Hi!

You should check if your DB tables contains keys on:

multikuli_authors -> authorid
multikuli_entrycat -> entryid
multikuli_category -> categoryid

multikuli_entries -> id, isdraft, timestamp, last_modified
multikuli_entryproperties -> entryid, property

Sadly, the multilingual plugin can't really be called only for specific entries, since the global function to retrieve blog entries is only run once. Indeed it might be worth thinking about creating dedicated static pages for these entries, and link them within your entry body.

The autosave plugin definitely has some issues with entryproperties, but sadly I do not really know that plugin very well and what it internally does. But I remember it recently got a fix committed to make it work with saving entryproperties, maybe this is a problem in your install now? Are you using the most recent version of the plugin?

HTH,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

peggylon
Regular
Posts: 51
Joined: Tue Jul 01, 2008 6:32 pm
Location: Berlin
Contact:

Re: performance issue with multilingual plugin

Post by peggylon » Mon Nov 21, 2011 10:32 am

Thank you Garvin, for the detailed index help. I'll check /set those. They won't be touched with any s9y or plugin-update? I'll keep the static page option as plan B for now...
The autosave plugin definitely has some issues with entryproperties, but sadly I do not really know that plugin very well and what it internally does. But I remember it recently got a fix committed to make it work with saving entryproperties, maybe this is a problem in your install now? Are you using the most recent version of the plugin?
I can't tell which autosave plugin version I was using. I remember I did update several plugins recently and after that autosave wasn't working anymore anyway. I would have de-installed the plugin anyway...

Thanks a lot,
Peggy
----------
peggylon aka multikulinaria http://www.multikulinarisch.es

Post Reply