Optimising Query

[dweb]

Hi

 

I have the following Query, but it can sometimes take over 2 minutes to return data

 

How would you suggest I improve the Query and which fields should I look at indexing in the database?

 

Thanks

 

 

 

SELECT user.id, user.name, user.email, user.myid                                                                            
                                                                                                                                                                
, (SELECT COUNT(*) as Num FROM stuff s WHERE s.userid = user.id) AS apps

, (SELECT COUNT(*) as Num FROM stuff s WHERE s.userid = user.id AND s.gone > 0) AS gone_total

, (SELECT COUNT(*) as Num FROM stuff s WHERE s.userid = user.id AND s.action = 3) AS exist_total
                                                                                                                                                                
, (SELECT COUNT(*) as Num FROM `customers` WHERE `userid` = user.id) AS customer_total
                                                                                                                                                                
FROM users user
                                                                                                                                                                
LEFT JOIN items i ON i.id = user.my_id
 

 

[Psycho]

You should typically index any fields that are used to JOIN tables or are commonly used as filtering (i.e. used in the WHERE clause). So, you would want to index a client_id, but not necessarily the client name - even if the name can be used for search criteria. In this case, you definitely want the user ID indexed in all the tables where it is used in these queries.

 

Without understanding the table structure and how the data exists, it is difficult to give you complete suggestions.

 

First off, I see you are doing a JOIN on the items table - but there is nothing from that table used in the query. So, it seems that is not needed - or you left something out. But, with the data for the "stuff" table I think you can get the results you want without all the subqueries - which is probably the biggest problem.

 

Since I don't have any data to test with I'm not 100% confident this will work, but give it a try (it may at least get you started int he right direction)

 

SELECT u.id, u.name, u.email, u.myid,
       COUNT(s.userid) AS apps,
       SUM(s.gone > 0)   AS gone_total,
       SUM(s.action = 3) AS exist_total,
       (SELECT COUNT(*) as Num FROM `customers` WHERE `userid` = u.id) AS customer_total
                                                                                                                                                                
FROM users AS u
LEFT JOIN stuff AS s ON s.userid = u.id
GROUP BY u.id

-- LEFT JOIN items i ON i.id = u.my_id

 

For the data from the "stuff" table I just JOINed all the records using the user id. Then for apps, take a count of the records by user id. For gone_total I take the sum of "(s.gone > 0)". That should be evaluated as a true (1) or false (0). So, it would be a count of all the records (by user id)  where that condition is true. I used the same logic for exists_total. However, I couldn't think of a way (right now) to remove the subquery for the customers table. If I was to JOIN that on the query it would corrupt the results from the stuff table.

Edited March 27, 2013 by Psycho

[Barand]

1. LEFT JOINS are slower than INNER JOINS - only use when necessary (and definitely not when totally unnecessary)

 

2. Avoid dependent subqueries (ie subqueries which depend on a value from the current record). In this case you are running 4 subqueries for every user record.

[fenway]

What's more, all the counts from the same table can likely be cominbed.