Databases
StackOverflow2013
Postsdbo
POFaster sql query then join
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFaster sql query then join
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USStarfish
    1. USPho3nixHun
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFaster sql query then join
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFaster sql query then join
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POFaster sql query then join
      1. This table or related slice is empty.
      1. VTUpMod
    1. COUse the `EXPLAIN` or `EXPLAIN EXTENDED` keywords to learn what your query is doing
      singulars
      1. POFaster sql query then join
      1. USAdrian Cornish
    2. COYou can't index a pattern in the middle of a column, such as `LIKE '%Outlook%'` (fulltext indices aside). As long as you keep using that condition, it'll be slow.
      singulars
      1. POFaster sql query then join
      1. USDCoder
    3. CO@DCoder you can tell I am not a DBA - I do not think in sets and your comment is so obvious now :-)
      singulars
      1. POFaster sql query then join
      1. USAdrian Cornish
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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