Will memcache help with expensive MySQL queries? (Drupal site)

Question

Hello all, this is a simple question. There are some queries on our site that take too many seconds to complete (like 15 seconds!). For anonymous traffic they get a cached .html file, but for logged in users it's too slow.

The query looks like this:

SELECT node.nid, votingapi_cache_vote_percent_average.value AS votingapi_cache_vote_percent_average_value, votingapi_cache_vote_percent_count.value AS votingapi_cache_vote_percent_count_value, node.type AS node_type, users.name AS users_name, users.uid AS users_uid, node_data_field_species.field_species_nid AS node_data_field_species_field_species_nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_created, node_data_field_picture.field_picture_fid AS node_data_field_picture_field_picture_fid, node_data_field_picture.field_picture_title AS node_data_field_picture_field_picture_title, node_data_field_picture.field_picture_alt AS node_data_field_picture_field_picture_alt, node_data_field_recorded.field_recorded_value AS node_data_field_recorded_field_recorded_value, node_data_field_static_location.field_static_location_value AS node_data_field_static_location_field_static_location_value, field_video_op_videos.video_id AS field_video_op_videos_preview_image_link FROM node node LEFT JOIN votingapi_cache votingapi_cache_vote_percent_average ON node.nid = votingapi_cache_vote_percent_average.content_id AND votingapi_cache_vote_percent_average.content_type = 'node' AND votingapi_cache_vote_percent_average.value_type = 'percent' AND votingapi_cache_vote_percent_average.tag = 'vote' AND votingapi_cache_vote_percent_average.function = 'average' LEFT JOIN votingapi_cache votingapi_cache_vote_percent_count ON node.nid = votingapi_cache_vote_percent_count.content_id AND votingapi_cache_vote_percent_count.content_type = 'node' AND votingapi_cache_vote_percent_count.value_type = 'percent' AND votingapi_cache_vote_percent_count.tag = 'vote' AND votingapi_cache_vote_percent_count.function = 'count' INNER JOIN users users ON node.uid = users.uid LEFT JOIN content_field_species node_data_field_species ON node.vid = node_data_field_species.vid LEFT JOIN content_type_picture node_data_field_picture ON node.vid = node_data_field_picture.vid LEFT JOIN content_field_recorded node_data_field_recorded ON node.vid = node_data_field_recorded.vid LEFT JOIN content_field_static_location node_data_field_static_location ON node.vid = node_data_field_static_location.vid LEFT JOIN content_type_video node_data_field_video ON node.vid = node_data_field_video.vid LEFT JOIN op_videos field_video_op_videos ON node_data_field_video.field_video_video_id = field_video_op_videos.video_id WHERE (node.type IN ('audio','picture','video')) AND (node.status = '1') ORDER BY votingapi_cache_vote_percent_average_value DESC, votingapi_cache_vote_percent_count_value DESC LIMIT 0, 30

Will memcache help to serve that page faster? Or, at least, second-time access to it...

Thanks

Answer 1

memcached is just a caching mechanism. Your query will still take just as long to perform.

If you want to use it to make things smoother, continually query the database and update the result in memcached in the background and serve up the information from the cache. That way the information is no more that 15 or so seconds old, but is retrieved very quickly.

You might also want to just increase the cache of the MySQL server. memcached is not usually used for caching a single query result...

Answer 2

Expensive queries should be chached one way or another. The query you posted didn't depend on the user being logged in so should be cached.

Have you tried turning on agressive caching in admin, this may help (but could hurt you elsewhere).

If the query is being run from a block make sure block caching is on and appropriate for the query (this would be BLOCK_CACHE_GLOBAL).

Worst case you should look at building your own caching for the pinch points. Only as a last resourt.

update: I am guessing that you are using views to generate the page. I have looked here and it looks like the latest views2 and views3 has some caching built in, if you upgrade you may be able to use it.

Answer 3

Using memcached to cache the output of this query, or the generated HTML resulting from it, will indeed speed up your page load after the initial result has been cached.

Before doing that though, you should look at the cache settings on the view itself. In my experience, these settings override the site-wide cache settings in Settings -> Performance. Assuming you are using Drupal 6 and Views 2, you can set the cache lifetime of a view by editing the view, and then clicking Basic Settings -> Cache.