CP text search is very very slow

Hi,

I just timed a text search on our website of 8000+ articles. I took 2 minutes and 40 seconds for cp search to complete. I ran it twice. By comparison, the native joomla search module took 3 to 4 seconds to return results.

Any ideas on what is happening and how I can make it faster?

This one definitely got our attention.
At first sight query structures are similar, when you only search by text in Custom Properties. We're setting up a bigger test installation of Joomla 1.7 to replicate and analyze the problem.

We made quite a few tests up to 10k articles and 20k tags assigned, on a very humble server (year 2007, 1GB RAM), and we've never been able to exceed 1.3 sec rendering included. According to our results search by text is not slower than standard Joomla search nor is slower than regular CP search.
The worst performance are given by a particular combination: all Custom Properties fields as checkboxes, all 20 checkboxes checked.

Hmm... The system admin guy sent me this error log.

"It was running for 2 minutes and then error above:
500 - Query execution was interrupted SQL=SELECT a.id, a.asset_id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, CASE WHEN badcats.id is null THEN a.state ELSE 0 END AS state, a.mask, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by, a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.images, a.urls, a.attribs, a.version, a.parentid, a.ordering, a.metakey, a.metadesc, a.access, a.hits, a.metadata, a.featured, a.language, a.xreference,c.title AS category_title, c.alias AS category_alias, c.access AS category_access,u.name AS author,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND( v.rating_sum / v.rating_count ) AS rating, v.rating_count as rating_count FROM fdos_content AS a LEFT JOIN fdos_categories AS c on c.id = a.catid LEFT JOIN fdos_users AS u on u.id = a.created_by LEFT JOIN fdos_contact_details AS contact on contact.user_id = a.created_by LEFT JOIN fdos_categories as parent ON parent.id = c.parent_id LEFT JOIN fdos_content_rating AS v ON a.id = v.content_id LEFT OUTER JOIN (SELECT cat.id as id FROM fdos_categories AS cat JOIN fdos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published <= 0 GROUP BY cat.id) AS badcats ON badcats.id = c.id WHERE a.id = 8051 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-01-18 22:15:25') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-01-18 22:15:25') AND (a.state = 1 OR a.state =2)
"

Our server has 4 gigs of ram too.. Any ideas on what we can do to figure this out?

Thanks

Foreword: that's not a Custom Properties query. If it was there would be joins with fdos_custom_properties, fdos_custom_properties_fields and fdos_custom_properties_values.

However, that's a fairly heavy query. You can see calculated fields (ROUND(...), several joins of all type, subqueries, a group by, and so no and so forth.
The common path to debug a query - first step toward optimization - is to analyze it to understand how it works, which keys are used and which are not.
To do so you have to run the query with the keyword 'EXPLAIN' before the actual query statement. Your query will become: You get a report that tells you how the query is executed. Interpreting this report and taking the appropriate corrective actions would definitely require a book on his own. Let's say that a single join on non-keyed fields or wrongly-keyed fields can slow down a query orders of magnitude.

Another important think to consider is how much memory is dedicated to PHP / MySQL / Apache. Default settings are usually very conservative - let alone those of a typical shared hosting - even today that RAM is no longer in scarce supply on servers.
Therefore you could end up having MySQL struggling to run queries and shuffling data to disk, while the most part of your RAM is left unused. Ditto for PHP.
PHP and MySQL configuration is beyond the scope of this forum , but I'd increase MySQL max_join_size, key_buffer_size if possible.

Hi Andreas,

Thanks for the detailed reply. My system admin asked me to relay this info to you:

"We have increased the max_join_size, to 16M
And key_buffer_size to 512

Now when running the search query 3 from 4 of our cpu are hanging in 100% usage"

Are these good settings or should they be even higher? Seems our site still hangs up when doing a text search. clicking on links via a cp menu returns results at reasonable speeds though.

Thanks