Slow browse for items and mpas

Hi

I'm trying to help our archivist <http://archives.library.wcsu.edu/omeka>. The browse items and browse maps are extremely slow. I've turned on the slow query log and this is what I'm getting:

# Query_time: 56  Lock_time: 0  Rows_sent: 1  Rows_examined: 66869772
SELECT COUNT(DISTINCT(tags.id)) FROM <code>omeka_tags</code> AS <code>tags</code>
 LEFT JOIN <code>omeka_records_tags</code> AS <code>records_tags</code> ON records_tags.tag_id = tags.id;

Look at the number of rows! There are only 2,500 item in the database, 5,542 tags and 12,065 record tags. I'm not familiar with the code base (nor php). Any hints would be appreciated.

He is running latest version of Omeka, but an old MySQL 5.0 (which I plan to update (but I don't think is the cause of the problem).

Thanks for an suggestions,

--Brian

One quick thing to try is to reduce the number of results per page from 25 down 10 or so. That's under Appearance -> Settings

OK! Thanks

No effect ;-(

Is that query coming up in the slow log when you go to the items browse page? That looks like the query to count the tags in the install, but that's not normally something that the browse page does.

John, Yes, if I do a browse it that query shows up in the slow query log. (I've got it set to 4 which I will set to 1 and see if other queries show up there).

I'm trying to track down the place where the "browse" query gets construct and see if I can tell exactly what being asked for but I'm not there yet.

Thanks for the reply. --Brian

Is it equally slow in the admin items browse?

Yes. it appears that any time it wants a list of items (browse) it executes that slow query. I just turned on the profiller to dump the queries to the browser for this one request but I haven't had time to look at. It did 83 queries! I don't know if anyone wants to look at this (it's long) but I'll include it ;-)

<p>[0.00035285949707031] SELECTfiles.* FROMomeka_filesASfiles`
INNER JOIN omeka_items AS items ON items.id = files.item_id WHERE (files.item_id = ?) GROUP BY files.id ORDER BY ISNULL(files.
<p>[0.00050520896911621] SELECT element_texts.* FROM omeka_element_texts AS element_texts WHERE (element_texts.record_type = 'Fil
<p>[0.00030779838562012] SELECT tags.*, COUNT(records_tags.id) AS tagCount FROM omeka_tags AS tags
LEFT JOIN omeka_records_tags AS records_tags ON records_tags.tag_id = tags.id WHERE (records_tags.record_id = 564) AND (records_ta
tags.id HAVING (COUNT(records_tags.id) > 0) ORDER BY tags.name ASC, tags.id ASC</p>
<p>[0.00031113624572754] SELECT tags.*, COUNT(records_tags.id) AS tagCount FROM omeka_tags AS tags
LEFT JOIN omeka_records_tags AS records_tags ON records_tags.tag_id = tags.id WHERE (records_tags.record_id = 564) AND (records_ta
tags.id HAVING (COUNT(records_tags.id) > 0) ORDER BY tags.name ASC, tags.id ASC</p>
<p>[0.00013303756713867] SELECT item_types.* FROM omeka_item_types AS item_types WHERE (item_types.id = 6) LIMIT 1</p>
<p>[0.00055289268493652] SELECT element_texts.* FROM omeka_element_texts AS element_texts WHERE (element_texts.record_type = 'Ite
<p>[0.00015401840209961]
SELECT COUNT(f.id)
FROM omeka_files f
WHERE f.item_id = ?</p>
<p>[0.00031709671020508] SELECT files.* FROM omeka_files AS files
INNER JOIN omeka_items AS items ON items.id = files.item_id WHERE (files.item_id = ?) GROUP BY files.id ORDER BY ISNULL(files.
<p>[0.00044608116149902] SELECT element_texts.* FROM omeka_element_texts AS element_texts WHERE (element_texts.record_type = 'Fil
<p>[0.00028681755065918] SELECT tags.*, COUNT(records_tags.id) AS tagCount FROM omeka_tags AS tags
LEFT JOIN omeka_records_tags AS records_tags ON records_tags.tag_id = tags.id WHERE (records_tags.record_id = 1500) AND (records_t
tags.id HAVING (COUNT(records_tags.id) > 0) ORDER BY tags.name ASC, tags.id ASC</p>
<p>[0.00029301643371582] SELECT tags.*, COUNT(records_tags.id) AS tagCount FROM omeka_tags AS tags
LEFT JOIN omeka_records_tags AS records_tags ON records_tags.tag_id = tags.id WHERE (records_tags.record_id = 1500) AND (records_t
tags.id HAVING (COUNT(records_tags.id) > 0) ORDER BY tags.name ASC, tags.id ASC</p>
<p>[0.00016188621520996] SELECT item_types.* FROM omeka_item_types AS item_types WHERE (item_types.id = 4) LIMIT 1</p>
<p>[0.0004270076751709] SELECT simple_pages_pages.* FROM omeka_simple_pages_pages AS simple_pages_pages WHERE (simple_pages_pages
<p>[0.00024104118347168] SELECT simple_pages_pages.* FROM omeka_simple_pages_pages AS simple_pages_pages WHERE (simple_pages_page
<p>Executed 83 queries in 56.758534908295 seconds</p>
<p>Average query length: 0.68383776997945 seconds</p>
<p>Queries per second: 1.4623351383911</p>
<p>Longest query length: 56.270206928253</p>
<p>Longest query:
SELECT COUNT(DISTINCT(tags.id)) FROM omeka_tags AS tags
LEFT JOIN omeka_records_tags AS records_tags ON records_tags.tag_id = tags.id</p>
slow.html 173,59 Bot

`

Thanks for your reply -- Brian

Here is the request data. It's not helping me but someone might see something here.

<h2>Request Data</h2>

<div>Request URI: <em>/omeka/items?sort_field=Dublin+Core%2CTitle&sort_dir=a</em></div>
<div>Params:<pre>Array
(
    [controller] => items
    [action] => index
    [module] => default
    [sort_field] => Dublin Core,Title
    [sort_dir] => a
)
</pre></div><h2>Session Data</h2><pre>Array
(
    [Default] => Array
        (
            [redirect] => /appearance/edit-settings
        )

    [Zend_Auth] => Array
        (
            [storage] => 3
        )

    [OmekaSessionCsrfToken] => Array
        (
            [token] => 07e65f980c2670a14f38dd2a66b9efaf
        )

)
</pre><h2>Server Data</h2><pre>Array
(
    [REDIRECT_SCRIPT_URL] => /omeka/items
    [REDIRECT_SCRIPT_URI] => http://archives.library.wcsu.edu/omeka/items
    [REDIRECT_STATUS] => 200
request.html                                                                                                         1,1            Top
"request.html" [noeol] 71L, 5524C

Thanks --Brian

Can you share the contents of the System Information link from the footer of the admin interface? That'll tell us all the relevant versions of things and plugins that you're using.

I don't know what's easier to read, the text or html.

User
Browser Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.71 Safari/537.36
Role super
System
Omeka 2.3.1
PHP 5.3.3 (apache2handler)
OS Linux 2.6.18-406.el5 i686
MySQL Server 5.0.95
MySQL Client 5.0.95
Apache Apache/2.2.3 (Red Hat)
PHP Extensions
Regular apache2handler, bz2, calendar, Core, ctype, curl, date, dom, ereg, exif, fileinfo, filter, ftp, gettext, gmp, hash, iconv, json, libxml, mysql, mysqli, openssl, pcre, PDO, pdo_mysql, pdo_sqlite, Phar, Reflection, session, shmop, SimpleXML, sockets, SPL, standard, tokenizer, wddx, xml, xmlreader, xmlwriter, xsl, zip, zlib
Plugins
BulkMetadataEditor 1.0.2
Coins 2.0.3
CollectionTree 2.0.2
CsvImport 2.0.3
DerivativeImages 2.0
DocsViewer 2.0
Dropbox 0.7.2
DublinCoreExtended 2.0.1
EmbedCodes 1.0
ExhibitBuilder 3.2.1
Geolocation 2.2.3
GettySuggest 1.3 (inactive)
HideElements 1.3
Html5Media 2.4
ItemOrder 2.0.2 (inactive)
LcSuggest 2.0.1
Neatline 2.4.2
NeatlineFeatures 2.0.5
NeatlineSimile 2.0.4
NeatlineTime 2.0.4
NeatlineWaypoints 2.0.2
OaiPmhRepository 2.0
OaipmhHarvester 2.0.1
OpenLayersZoom 2.6 (inactive)
PdfText 1.0.1
RecordRelations 2.0 (inactive)
Reports 2.0.1
SearchByMetadata 1.2
ShortcodeCarousel 1.0
SimpleContactForm 0.4
SimplePages 3.0.6
UserProfiles 1.0 (inactive)
Themes
avantgarde 1
berlin 2.3
default 2.3.1
emiglio
minimalist 2.0
neatscape 1.0-rc.2 (current)
rhythm 2.0
santa-fe 2.0
seasons 2.3

There's a few interesting things happening here: the first is that the items browse shouldn't even be executing that count query. That's why I wanted the list of plugins, and asked about public vs. admin. I don't see any plugins in your list that particularly jump out at me, so my next advice on that front would be to just deactivate the plugins one by one and test the browse after each one to see if any of them is the culprit.

The second interesting thing is that the query is so extremely slow. Can you run an EXPLAIN of the slow query in MySQL and post the results? It should help shed some light on why it's executing so slowly. There's some obvious extra pointless work happening in that query that we should be able to cut out, but it still shouldn't be that crazily slow. I'm interested to see what's happening in terms of indexes being used and things like that.

Does going to the Tags page on the admin, which also counts the tags, take similarly long?

John, Thanks for your reply. Yes, tags page in admin is the same. It appears to affect any browse anywhere. I'll cycle through those plugins and run the EXPLAIN on Monday. Thank you for your time. -- Brian

BTW, this is unrelated to the slow browse issue, but in exploring it I noticed that there is a fix to the SearchByMetadata plugin that hadn't been released. It should make it so the links on the browse page go to the item, not back to the browse page. I just posted the fixed version.

Thanks Patrick I'll take a look at it.

I unloaded all the plugins but got the same result.

I just ran EXPLAIN on that query and this is what I got

+----+-------------+--------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+--------------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | tags         | index | NULL          | PRIMARY | 4       | NULL |  5542 | Using index |
|  1 | SIMPLE      | records_tags | index | NULL          | tag     | 160     | NULL | 12072 | Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+-------+-------------+

It's not finding anything to to match on so I guess I need to build an index on omega_records_tags.record_id to match the primary key of omega_tags.

Thanks for your help,

--Brian

Have you modified the core code at all?

If you deactivated all your plugins, you shouldn't be getting any "count the tags" query on the admin side items browse for sure. I went and confirmed on my own installation: we simply don't run that query on the regular items browse.

As for the indexes: an index on just the tag_id column for the records_tags table is probably what will make the biggest difference here, with the query itself left unchanged.

I unloaded the plugins and simple went to the browse screen and got the same results. I didn't look around too much but I can do it again.

I haven't changed any code and as far as I know, no one else has. I'm gonna add the index to that table and see if that helps if not I'm gonna back up and do a clean install

Thanks for your time,

--Brian

--Brian

Adding the index did the trick! Thanks both of you for your help.

--Brian