So, this is what I have:
A simple table
|class |student| --------------- Math Alice Math Bob Math Peter Math Anne Music Bob Music Chis Music Debbie ...
There's 1000000 classes. Every student attends 500 classes. (Bad analogy, I know...) For testing, there's 4000 students in the DB (so there is 2.000.000 rows), but this DB should handle several million students, that's why I'm using a MySQL ndbcluster.
Anyway... a query like this:
SELECT student, COUNT(class) as common_classes FROM classes_table WHERE class IN (my_subject_list) GROUP BY student ORDER BY common_classes DESC
resulting in something like:
|student |commonClasses| Brad 6 Melissa 4 Chris 3 Bob 3 ...
takes about 1 second with a InnoDB engine on one server, which is ok. On a ndbcluster with 3 datanodes the same query takes up to 10 seconds, which is far too much. I don't know how the above statement is treated internally, but I guess that there is a lot of communication between the nodes, which makes it slow due to latency. Can someone tell me, what happens in the cluster when I perform this query? How can I make it faster?
Note: this is a question that came up after I posted this question: https://stackoverflow.com/questions/9095054/how-do-i-compute-a-ranking-with-mysql-stored-procedures For more informations, have a look there!