Query can not be executed, or waiting too long

I am using MySQL 5.0 and working with some crowded tables. I actually want to calculate something and
wrote a query like this:

SELECT 
   shuttle_payments.payment_user as user, 
   SUM(-1 * (shuttle_payments.payment_price + meal_payments.payment_price ) + 
   print_payments.payment_price) as spent 
FROM 
   ((shuttle_payments 
   INNER JOIN meal_payments ON shuttle_payments.payment_user = meal_payments.payment_user) 
   INNER JOIN print_payments ON meal_payments.payment_user = print_payments.payment_user) 
GROUP BY 
   shuttle_payments.payment_user 
ORDER BY 
   spent DESC 
LIMIT 1

Well, there are 3 tables here and have approx. 60,000 rows per table. Is it taking too long because tables are so crowded (so should I transfer to NoSQL or sth) or it is a normal query but my server is taking too long because its CPU is weak? Or my query is wrong?

I want this query to sum all price columns from three tables and found which user spent the most money.

Thanks for your time 🙂

Answer

It looks like your query is Ok. You have to check whether there are indexes present on these three tables or not. Please create indexes like-

CREATE INDEX idx_shuttle_payments ON shuttle_payments(payment_user);
CREATE INDEX idx_meal_payments ON meal_payments(payment_user);
CREATE INDEX idx_print_payments ON print_payments(payment_user);

Above statements will create non-clustered indexes on payment_user column.

if payment_user data type is BLOB/Text then –

CREATE INDEX idx_shuttle_payments ON shuttle_payments(payment_user(100));
CREATE INDEX idx_meal_payments ON meal_payments(payment_user(100));
CREATE INDEX idx_print_payments ON print_payments(payment_user(100));

In above statements, I have set prefix length to 100. You have to decide this prefix length as per your data.

From MySQL documentation:

BLOB and TEXT columns also can be indexed, but a prefix length must be given.