Optimize a mysql query

Question

I have 5 tables in my DB: user, log1, log2, log3 and log4. Each log saves data about the user (log1 is logins log, etc.) - each log has UserID and CreateTime and LogID as his columns.

I want to create a query that is fast enough, that will show me the number of times each log has this user id. something like:

select u.UserID, Count(log1.CreateTime) as l1, ... Count(log4.CreateTime) as l4 
from users u left join log1 on log1.UserID = u.UserID left join ...
group by u.UserID

The problem is that this query take too much time.

Can anyone help me optimize my query?

Review the execution plan for your query to determine where things are slow. My guess is that you need some indexes somewhere. — Rick S
– Rick S, Commented Mar 28, 2016 at 14:23

Gordon Linoff · Accepted Answer · 2016-03-28 14:44:46Z

2

For this query:

select u.UserID, Count(log1.CreateTime) as l1, ... Count(log4.CreateTime) as l4 
from users u left join
     log1
     on log1.UserID = u.UserID left join ...
group by u.UserID

You want indexes on the "log" tables: log1(UserId), etc.

However, I don't think this is what you are really looking for. With the same indexes, you should use correlated subqueries:

select u.*,
       (select count(*) from log1 where log1.UserID = u.UserID) as log1_cnt,
       . . .
from users u;

(This assumes that the creation date/time column is never NULL.)

This is better for several reasons:

It does the correct counts. Your version produces the same count (or 0) for the four columns.
This does not create an intermediate Cartesian product for a given user for the records from the four tables.
This does not use a group by in the outer query.
Indexes can be used for the correlated subqueries.

edited Mar 28, 2016 at 14:44

answered Mar 28, 2016 at 14:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hasan BINBOGA · Accepted Answer · 2016-03-28 14:31:10Z

0

I think, you want to get statistics of your log records.

We have log databases, insert about 1 millon records per day. We create statistic database and add trigger on log tables for insert. So we update or insert statistic record for each log insert.

answered Mar 28, 2016 at 14:31

Hasan BINBOGA

83210 silver badges21 bronze badges

Collectives™ on Stack Overflow

Optimize a mysql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related