Mysql – Query user session length on a timestamped activity log

You can simplify your query to something along these lines. I expect MySQL will generate a simpler execution plan.

SELECT date(date) period, count(*) clicks
FROM visits
WHERE url_id = 3
  AND DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH ) 
GROUP BY period;

If returning results on high-traffic links in one second is a hard requirement, you might need to upgrade your hardware.

Expect to need an index on each column used in a WHERE clause. You might benefit from some multicolumn indexes; {url_id, date} is a candidate.

Test at scale, if that's at all possible. (It's usually possible, although it might take some time.) Use EXPLAIN to see what MySQL is doing with your queries.

You don't have to query browsers, countries, and everything else all at one time. When I was running web development, I rarely looked at countries--they weren't relevant to the niche I was working in. Also consider other asynchronous UI technologies.

PostgreSQL has a better optimizer. It might work better than MySQL. Test it.

Query 1, tested in SQLFiddle-1

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,          -- the first bed a patient uses
                                           -- can be omitted
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
  ( SELECT patient_id, bed_id, admitted, discharged
    FROM t 
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS prev 
            WHERE prev.ward_id = @ward_id_to_check
              AND prev.patient_id = t.patient_id
              AND prev.discharged = t.admitted
          )
  ) AS st
JOIN
  ( SELECT patient_id, admitted, discharged
    FROM t 
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS next 
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = t.patient_id
              AND next.admitted = t.discharged
          )
  ) AS en
    ON  st.patient_id = en.patient_id
    AND st.admitted <= en.admitted
GROUP BY
    st.patient_id,
    st.admitted ;

Query 2, which is the same as 1 but without the derived tables. This will probably have better execution plan, with proper indexes. Test in SQLFiddle-2:

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
    t AS st    -- starting period
  JOIN
    t AS en    -- ending period
      ON  en.ward_id = @ward_id_to_check
      AND st.patient_id = en.patient_id
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS next 
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = en.patient_id
              AND next.admitted = en.discharged
          )
      AND st.admitted <= en.admitted
WHERE 
      st.ward_id = @ward_id_to_check
  AND NOT EXISTS
      ( SELECT * 
        FROM t AS prev 
        WHERE prev.ward_id = @ward_id_to_check
          AND prev.patient_id = st.patient_id
          AND prev.discharged = st.admitted
      )
GROUP BY
    st.patient_id,
    st.admitted ;

Both queries assume that there is a unique constraint on (patient_id, admitted). If the server runs with strict ANSI settings, the bed_id should be added in the GROUP BY list.