I removed a node from the database and now my query runs faster. Why?
Hi folks. I'm trying a little scaling experiment where I'm removing a node to see how a test query's performance changes. When I removed a node from the database (bringing the DB Size (#Nodes) from 4 to 3, the query actually runs /faster/ than with 4: Instead of taking ~8 minutes it now runs in ~5. The query does lots of joins. I'd really appreciate someone explaining how this is possible. I've posted the query plus the two different PROFILE outputs at https://gist.github.com/anonymous/9487818 . I've also included the query below. Thank you.
SELECT relVarTable0.id AS id, relVarTable1.val, relVarTable2.val
FROM (SELECT id FROM rates) relVarTable0
(SELECT rates1.id AS id, AVG(rates4.rating) AS val
FROM rates rates1, movie movie1, rates rates2, ml_user ml_user1, rates rates3, movie movie2, rates rates4
WHERE movie1.id = rates1.movie_id AND movie1.id = rates2.movie_id AND ml_user1.id = rates2.ml_user_id AND ml_user1.id = rates3.ml_user_id AND movie2.id = rates3.movie_id AND movie2.id = rates4.movie_id AND movie1.id <> movie2.id AND rates1.id <> rates2.id AND rates2.id <> rates3.id AND rates3.id <> rates4.id AND rates4.rating IS NOT NULL
GROUP BY rates1.id) relVarTable1
ON relVarTable0.id = relVarTable1.id
(SELECT rates1.id AS id, rates1.rating AS val
FROM rates rates1
WHERE rates1.rating IS NOT NULL ) relVarTable2
ON relVarTable0.id = relVarTable2.id;