Q:

MySQL GROUP BY taking so much time to fetch records

I want to query the database to fetch the last visit time of every user here is the query:
SELECT 
u.user_id,
u.firstname,
u.lastname,
u.email,
pv.visit_time 
FROM
  users u 
LEFT OUTER JOIN pageviews pv 
    ON u.user_id = pv.user_id 
   GROUP BY pv.user_id 
LIMIT 0, 12 

This query is taking 30 to 40 seconds to execute on live server, however if i remove the GROUP BY clause then it is taking 3 to 6 seconds but with duplicate records.  Any idea what's wrong with this query? 
Also i have tried DISTINCT but found same issue. 
Thanks, any help would be appreciated. 

A:

What are your indexes? 
Do you really want a left join, as that would seem irrelevant?  Using a LEFT OUTER JOIN it would just seem that you are going to get a row for user_id of NULL, but with nulls also in the other columns. 
Further you are using GROUP BY to return a single row for each user.  However which row is returned is not defined, so it could be any page views visit_time that is brought back for a user. 
Also you have only a single column in the GROUP BY clause but other non aggregate columns in the select.  With default options in MySQL this will work but will not work in most flavours of SQL and will also not work when MySQL is performing the group by in strict mode (see this manual page ). 
Add a index on u.user_id and a compound index on pv.user_id AND pv.visit_time.  Then assuming you want the latest visit time for each user try the query as:-
SELECT u.user_id,
    u.firstname,
    u.lastname,
    u.email,
    MAX(pv.visit_time)
FROM users u 
INNER JOIN pageviews pv 
ON u.user_id = pv.user_id 
GROUP BY u.user_id, u.firstname, u.lastname, u.email
ORDER BY u.user_id
LIMIT 0, 12

(strictly speaking the ORDER BY clause is not required as it is implicitly done by the GROUP BY clause, but it does make it more explicit what is expected to anyone reading the code in future).