Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I have a large userbase in my Drupal 7 site, which contains a People finder. Because there are hundreds of thousands of users and we want to display the People finder in a random order on page load, I presume it's transferring the whole userbase into memory at some point, in order to paginate (using the Pager built-in to Views2 Views3).

I've noticed that the query has no limit against it (presumably because of pagination) and that it joins against users_roles twice (because I need to include all users of rôle A but not also of rôles B, C and D). I've added comments against the WHERE clause to explain each line's purpose and how it's come out of my View.

SELECT
    users.uid AS uid,
    'user' AS field_data_field_user_image_user_entity_type,
    'user' AS field_data_field_user_field1_user_entity_type,         -- custom field
    'user' AS field_data_field_user_field2_display_user_entity_type, -- custom field
    'user' AS field_data_field_user_field3_user_entity_type,         -- custom field
    'user' AS field_data_field_user_field4_user_entity_type,         -- custom field
    RAND() AS random_field
FROM
    users users
    INNER JOIN users_roles users_roles ON users.uid = users_roles.uid
    LEFT JOIN users_roles users_roles2
        ON users.uid = users_roles2.uid
        AND (users_roles2.rid = :views_join_condition_0
          OR users_roles2.rid = :views_join_condition_1
          OR users_roles2.rid = :views_join_condition_2)
WHERE
    (( (users.status <> :db_condition_placeholder_3)      -- Active users only
    AND (users_roles.rid = :db_condition_placeholder_4)   -- Must be in rôle A
    AND (users_roles2.rid IS NULL)                        -- Must not be in rôles B, C, D
    AND (users.uid != :users_uid OR users.uid IS NULL) )) -- Must not be current user
ORDER BY random_field ASC

So I have two questions:

  1. Is there some way in Views2 that I can somehow get this query to be limited (potentially by sorting on something else that pretends to be random)? I assume that transferring fewer result rows per query is likely to help with the memory overhead?
  2. If I want to mess with that WHERE clause, am I better off using a PHP Filter Criterion to do the roles messing (which would presumably remove the double-join) or should I move the whole damn query into either a custom module[a] or a mySQL view[b]?

a: I assume that would attach to the relevant hook with a function modulename_views_pre_execute(&$view).
b: I assume a mySQL view would still need to do both joins, one inner join and one left join, so I can't quite see how this would help.

For what it's worth, I know that my site will have a relatively short lifetime and, during that, the rôles won't change, so I am happy to hardcode the rôle IDs, if that will give me a performance benefit.

share|improve this question
    
I realise I could also avoid the joining by doing something like SELECT * FROM users WHERE uid IN (SELECT DISTINCT uid FROM users_roles WHERE rid = 5) AND uid NOT IN (SELECT DISTINCT uid FROM users_roles WHERE rid IN (6,8,9)). But that's still going to pull back all the rows; would that be better than the double-join? –  Owen Blacker Mar 29 '12 at 12:47
1  
Are you using Views 2 or Views 3? I didn't think that Views 2 ever had a version for Drupal 7. –  MPD Mar 29 '12 at 12:58
    
@MPD You're right, it's Views3; I thought it was Views2. My bad. –  Owen Blacker Mar 29 '12 at 13:07
    
Just a thought: Do you use indexes on the joined ids and the fileds in the where clause? Using indexes in the right way can speed up your queries dramatically. –  BetaRide Mar 29 '12 at 13:16
    
@BetaRide Yes, the JOINed IDs are both indexed (indeed there are only two columns in users_roles and they are a clustered primary key together). I'll double-check users.status but I'm pretty sure it's the ugly join that's the problem here. –  Owen Blacker Mar 29 '12 at 13:49
show 1 more comment

2 Answers 2

Are you 100% positive that the view isn't paging in SQL? When you turn on Query Logging in the Devel module, what do you see for your view? I just double checked a site and am seeing (formatting is mine):

SELECT node.nid AS nid, RAND() AS random_field
FROM node node
WHERE (( (node.status = :db_condition_placeholder_0)
  AND (node.type IN (:db_condition_placeholder_1)) ))
ORDER BY random_field ASC LIMIT 10 OFFSET 0

when I am on paged view with a random sort.

I have seen the double JOIN thing before. Often, you can adjust the view parameters and get the query to run the way you want.

Optimizing these queries is really use dependent. I am pretty sure that both of the columns you are joining on are both indexed already. You can try to replicate the query manually, and then EXPLAIN it, and manually improve it. You can then implement this query in a custom block, but then you lose out on other things that Views give you. Caching the view results, may help, too.

Keeping everything in MySQL is likely best (it is meant for heavy lifting like this), but only profiling will give the true results for your system. Just make sure you have MySQL, Apache, and PHP tuned so that you aren't chasing the wrong problem.

share|improve this answer
    
We've been looking at the site performance for a couple of days and it's definitely this query (and one other) on this page that are our main problem. The query in the question is copy/pasted from the output of Devel and then reformatted for legibility. I did look at EXPLAIN, but part of my problem is that I can't see how to improve it dramatically from the status quo — I've also listed the mySQL bit on DBA.SE and the subquery option looks slightly better, but I don't quite follow how to use the output of EXPLAIN, tbh. –  Owen Blacker Mar 29 '12 at 13:52
    
I just find it very odd that the ORDER/LIMIT is missing from yours, but I see it when I use "Global: Random" as a views sort. –  MPD Mar 29 '12 at 14:02
    
Yeah, I'm sure that ORDER/LIMIT would help here dramatically. But I can't see how to enable them. Is there something I need to do to the Query settings, perhaps? (I have all three checkboxes unticked, so SQL rewriting is not disabled) –  Owen Blacker Mar 29 '12 at 14:14
    
No clue. Another module may be rewriting the query. I would try remaking the view w/ a new name and see what happens. Other than that, you can take @Countzero's advice and try to alter the query. –  MPD Mar 29 '12 at 14:49
    
Remaking the View with a new name doesn't make any difference, it would seem. It takes pretty much the same time to run each query and there's still no LIMIT/OFFSET appearing in the SQL query being shown me by Devel. –  Owen Blacker Mar 29 '12 at 15:42
add comment

You can easily modify the query in hook_views_query_alter, add your LIMIT clause and see what you get.

But MPD advice above is very sound and is widely applicable.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.