Hello team alpha-pager,

I've created a user directory using views alpha pager. Everything is working nicely except the performance: A basic usernode alpha paged list of about 4500 users, paginated to 20 nodes per page is taking 45 seconds to load. This is when clicking on the "All" link. Performance is similar when clicking on letters.

Using the devel module, it seems the top three taxing queries are related to the alpha pager:


Executed 223 queries in 44956.78 milliseconds. Queries taking longer than 200 ms and queries executed more than once, are highlighted. Page execution time was 45824.71 ms.
ms	#	where	query

15136.49	1	pager_query	SELECT node.nid, usernode_users.access AS usernode_users_access, usernode_users.uid AS usernode_users_picture, usernode_users.name AS usernode_users_name, usernode_users.uid AS usernode_users_uid, usernode_users.created AS usernode_users_created, userpoints.points AS userpoints_points FROM node node INNER JOIN usernode usernode ON node.nid = usernode.nid LEFT JOIN users usernode_users ON usernode.uid = usernode_users.uid LEFT JOIN userpoints userpoints ON node.uid = userpoints.uid WHERE (node.type IN ('usernode')) ORDER BY usernode_users_access DESC, usernode_users_access DESC, usernode_users_access DESC LIMIT 0, 20

14878.92	1	pager_query	SELECT count(node.nid) FROM node node INNER JOIN usernode usernode ON node.nid = usernode.nid LEFT JOIN users usernode_users ON usernode.uid = usernode_users.uid LEFT JOIN userpoints userpoints ON node.uid = userpoints.uid WHERE (node.type IN ('usernode'))

14745.33	1	views_alpha_pager_views_query_alter	SELECT DISTINCT LEFT(TRIM(LEADING 'AND ' FROM (TRIM(LEADING 'THE ' FROM (TRIM(LEADING 'A ' FROM (TRIM(LEADING '\'' FROM (TRIM(LEADING '"' FROM (TRIM(LEADING ' ' FROM (UPPER(usernode_users.name))))))))))))), 1) FROM node node INNER JOIN usernode usernode ON node.nid = usernode.nid LEFT JOIN users usernode_users ON usernode.uid = usernode_users.uid LEFT JOIN userpoints userpoints ON node.uid = userpoints.uid WHERE (node.type IN ('usernode')) ORDER BY LEFT(TRIM(LEADING 'AND ' FROM (TRIM(LEADING 'THE ' FROM (TRIM(LEADING 'A ' FROM (TRIM(LEADING '\'' FROM (TRIM(LEADING '"' FROM (TRIM(LEADING ' ' FROM (UPPER(usernode_users.name))))))))))))), 1)

I guess my questions are the following:

1. Does this seem correct? Is anyone out there getting better performance?
2. Are there any caching options? I tried caching the view in a panel, however it only caches the first alpha link you click on.

Any advice would be greatly appreciated,
Mike

Comments

mpaler’s picture

Status: Active » Fixed

Figured this out on my own (with some help from Merlinofchaos). My view included userpoints for that user and that was bogging things way down. By adding an index to the userpoints.uid column it sped up the query to less than .5 second.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.