1

What my query is doing:

I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current member-sessions/logins and with my statistics table I also want to see member-logins of the past. Thus I want to store these data into my bot_sessions_statistics table as well to make sure they are not being updated anymore:

The original query:

INSERT INTO bot_sessions_statistics (member_id, session_token, username, ip_address)
SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN
(
    SELECT stats.member_id, stats.session_token, stats.username, stats.ip_address
    FROM bot_sessions_statistics as stats
    WHERE date_active >= date(NOW())
) AS stats
ON sessions.member_id = stats.member_id
WHERE latest_renewal >= date(NOW())
AND stats.member_id IS NULL

The performance of the original query:

Query_time: 86.364613  Lock_time: 0.000085 Rows_sent: 0  Rows_examined: 1088312551

The table structures:

Table bot_sessions: Table bot_sessions

Table bot_sessions_statistics: Table bot_sessions_statistics

SHOW CREATE of both tables: http://pastebin.com/UUzEPX5v

The performance of single queries:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE latest_renewal >= date(NOW())

Returns 44.2k rows (Duration: 0.078s / Fetch: 1.607s)

SELECT stats.member_id, stats.session_token, stats.username, stats.ip_address
FROM bot_sessions_statistics as stats
WHERE date_active >= date(NOW())

Returns 23.3k rows (Duration: 0.047s / Fetch 0.920s)

4
  • Have you considered partitioning your table by YEAR(date_active) or something? This will split the data files into specific file sets. I use it extensively for time series based analysis in mysql.
    – cerd
    Aug 26, 2015 at 19:16
  • I can't do this because of other reasons/queries
    – kentor
    Aug 26, 2015 at 20:18
  • Could you please post your table definitions as the result of "SHOW CREATE TABLE My_Table \G - this makes it easy to cut and paste, thanks.
    – Vérace
    Aug 26, 2015 at 20:27
  • @Vérace I have edited my thread and added this :).
    – kentor
    Aug 26, 2015 at 20:33

2 Answers 2

1

As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not. At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows.
I guess your query will at least require an index on latest_renewal (but, again, check with EXPLAIN).

This is my understanding of your query, on which I based my simplification:

SELECT sessions.member_id
    ,sessions.session_token
    ,sessions.username
    ,sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN ( /* */
    SELECT stats.member_id
        ,stats.session_token
        ,stats.username
        ,stats.ip_address
    FROM bot_sessions_statistics AS stats
    WHERE date_active >= DATE (NOW()) /* a. I think this gives all of 'today' statistics */
    ) AS stats ON sessions.member_id = stats.member_id /* b. you're joining on 'member_id' which is NOT NULL in sessions, so any row from this subquery will have a value for (stats.)member_id */
WHERE latest_renewal >= DATE (NOW())
    AND stats.member_id IS NULL /* c. this will filter out all the rows from the JOIN, since no stats.member_id can be NULL */

Next, at first sight (if I'm not mistaken) I think your SELECT query can be rewritten without the LEFT JOIN, like this:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE 
    NOT EXISTS (
        SELECT 1 FROM bot_sessions_statistics
        WHERE date_active >= date(NOW())
    )
AND latest_renewal >= date(NOW())

If the above is really correct, compare the results of EXPLAIN on both.

8
  • Thank you very much for that answer, I haven't seen EXPLAIN so far but I will provide the information for my query as well. I also checked the performance of your query which is much better (0.5s). Can you explain what SELECT 1 is doing? I also tried to perform a query with WHERE member_id IS NOT IN ( SELECT ... )but it was even worse than my current query is.
    – kentor
    Aug 27, 2015 at 0:28
  • By the way I am using MyISAM as Storage engine, as far as I know I can't create more than PKs with MyISAM or?
    – kentor
    Aug 27, 2015 at 0:30
  • @kentor Then this and this other link will give you lots of informations. SELECT 1 just returns 1 from each row satisfying the WHERE, instead of the actual row data (that may be provided by an index or, worse from a performance point of view, could require physical access to the row), since you're not using that data - notice, however, that the optimizer could already be doing that for you. Nice, but please check carefully that my rewritten version is really correct.
    – watery
    Aug 27, 2015 at 0:35
  • @kentor About your second comment, you can add other indexes with MyISAM too - you're already doing that on the bot_sessions_statistics table :-)
    – watery
    Aug 27, 2015 at 0:37
  • 1
    In MySQL left join is usually preferred to not exists as it is better optimized in older versions.
    – jkavalik
    Aug 27, 2015 at 8:13
1

Based on the explain image above, the query is doing a table scan. (possible key and key are Null). Rows_examined: 1,088,312,551 ( 1 billion rows examined, that's quite a lot, look like a cross join to me). The second query (not exist) might not be right as it will exclude all data FROM bot_sessions_statistics WHERE date_active >= date(NOW()) compare to a left JOIN (original query) which usually returns all rows, including the ones not matching the parent table criteria query.

Also for your sub-query, since you don't need session_token, username, you don't need it:

SELECT stats.member_id, stats.session_token, stats.username, stats.ip_address
FROM bot_sessions_statistics as stats
WHERE date_active >= date(NOW())

should be

  SELECT stats.member_id
    FROM bot_sessions_statistics as stats
    WHERE date_active >= date(NOW())

Tips: Try to avoid date > or date >= (and < , <=). Mysql will scan the table. Use between instead. Make sure you have index on these date fields.

Partitioning could also be a good solution.It's great for queries and for purging data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.