StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFaster sql query then join
text
Body
copied!<p>I have a big table with more than 10,000 rows and it will grow to 1,000,000 in the near future, and I need to run a query which gives back a Time value for each keyword for each user. I have one right now which is quite slow because I use left joins and it needs one subquery / keyword:</p> <pre><code>SELECT rawdata.user, t1.Facebook_Time, t2.Outlook_Time, t3.Excel_time FROM rawdata left join (SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Facebook_Time' FROM rawdata WHERE MainWindowTitle LIKE '%Facebook%' GROUP by user)t1 on rawdata.user = t1.user left join (SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Outlook_Time' FROM rawdata WHERE MainWindowTitle LIKE '%Outlook%' GROUP by user)t2 on rawdata.user = t2.user left join (SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Excel_Time' FROM rawdata WHERE MainWindowTitle LIKE '%Excel%' GROUP by user)t3 on rawdata.user = t3.user </code></pre> <p>The table looks like this:</p> <pre><code>WindowTitle | StartTime | EndTime | User ------------|-----------|---------|--------- Form1 | DateTime | DateTime| user1 Form2 | DateTime | DateTime| user2 ... | ... | ... | ... Form_n | DateTime | DateTime| user_n </code></pre> <p>The output should looks like this:</p> <pre><code>User | Keyword | SUM(EndTime-StartTime) -------|-----------|----------------------- User1 | 'Facebook'| 00:34:12 User1 | 'Outlook' | 00:12:34 User1 | 'Excel' | 00:43:13 User2 | 'Facebook'| 00:34:12 User2 | 'Outlook' | 00:12:34 User2 | 'Excel' | 00:43:13 ... | ... | ... User_n | ... | ... </code></pre> <p>And the question is, which is the fastest way in MySQL to do this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload