Problem/Motivation
On one of D7 project we implement gallery with random sort via views . (~17k multilangage nodes tagged ~400 taxonomy terms from hierartical vocabulary )
Following SQL was generated
SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, 'node' AS field_data_field_preview_node_entity_type, 'panel_pane_1' AS view_name, RAND() AS random_field
FROM
{node} node
LEFT JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid AND (taxonomy_index.tid = '16020' OR taxonomy_index.tid = '15822' OR taxonomy_index.tid = '15967' OR taxonomy_index.tid = '15969' OR taxonomy_index.tid = '15970' OR taxonomy_index.tid = '15980' OR taxonomy_index.tid = '15982' OR taxonomy_index.tid = '15983' OR taxonomy_index.tid = '15984' OR taxonomy_index.tid = '15985' OR taxonomy_index.tid = '15979' OR taxonomy_index.tid = '15971' OR taxonomy_index.tid = '15972' OR taxonomy_index.tid = '15973' OR taxonomy_index.tid = '15974' OR taxonomy_index.tid = '15975' OR taxonomy_index.tid = '15977' OR taxonomy_index.tid = '15981' OR taxonomy_index.tid = '15976' OR taxonomy_index.tid = '15978' OR taxonomy_index.tid = '15968' OR taxonomy_index.tid = '15986' OR taxonomy_index.tid = '15827' OR taxonomy_index.tid = '15987' OR taxonomy_index.tid = '15988' OR taxonomy_index.tid = '15989' OR taxonomy_index.tid = '15993' OR taxonomy_index.tid = '15990' OR taxonomy_index.tid = '15991' OR taxonomy_index.tid = '15992' OR taxonomy_index.tid = '15823' OR taxonomy_index.tid = '16006' OR taxonomy_index.tid = '16007' OR taxonomy_index.tid = '16008' OR taxonomy_index.tid = '16009' OR taxonomy_index.tid = '16010' OR taxonomy_index.tid = '16011' OR taxonomy_index.tid = '16012' OR taxonomy_index.tid = '16013' OR taxonomy_index.tid = '16014' OR taxonomy_index.tid = '16015' OR taxonomy_index.tid = '16016' OR taxonomy_index.tid = '16017' OR taxonomy_index.tid = '16018')
WHERE (( (node.nid IN (SELECT tn.nid AS nid
FROM
{taxonomy_index} tn
LEFT OUTER JOIN {taxonomy_term_hierarchy} th ON th.tid = tn.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th1 ON th.parent = th1.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th2 ON th1.parent = th2.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th3 ON th2.parent = th3.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th4 ON th3.parent = th4.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th5 ON th4.parent = th5.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th6 ON th5.parent = th6.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th7 ON th6.parent = th7.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th8 ON th7.parent = th8.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th9 ON th8.parent = th9.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th10 ON th9.parent = th10.tid
WHERE ( (tn.tid = '15809') OR (th1.tid = '15809') OR (th2.tid = '15809') OR (th3.tid = '15809') OR (th4.tid = '15809') OR (th5.tid = '15809') OR (th6.tid = '15809') OR (th7.tid = '15809') OR (th8.tid = '15809') OR (th9.tid = '15809') OR (th10.tid = '15809') ))) )AND(( (node.status = '1') AND (taxonomy_index.tid IS NULL ) AND (node.language IN ('ru')) )))
ORDER BY random_field ASC
LIMIT 20 OFFSET 0
views statistics shows that this sql request time 260-290ms and on investigating we found that the reason - random sort.
Last changes in random sort plugin dated 2012-03-08
Proposed resolution
Implement alternative random methods in sort plugin optimized views for performance.
In the internet I found this old article about random "order by" optimisation with benchmarks.
We check benchmark results for Inner Join Method and got performance improvement up to 10 times on query above (23- 24 ms) according to views statistics. Page generate time was reduced up to twice.
Remaining tasks
implement patch
User interface changes
Make views sort plugin configurable.
API changes
None
Data model changes
None