Mysql – Optimize very slow SQL joins on multiple tables or use different engine

join;MySQLnosql

We have something similar to Google Analytics, but decided to not use something that's already available (but didn't fit our needs exactly), and instead created our own "mini-analytics".

Now, this was all easy and fun, but as it grows, the architecture either wasn't designed properly, or the wrong tools were used to solve the problem.

The problem lies with queries that look like the following: "Get all user sessions that have the following events: login, chrome browser version 58, and profile_view".

Currently this hits the following tables:

CREATE TABLE `logins` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `login_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `browsers` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `browser_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `profile_views` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `profile_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

Some notes:

  • All the mediumblob columns are JSON objects, but we haven't upgraded to MySQL 5.7.8 yet.
  • All tables have the same columns and indexes.
  • Each table contains in between several million and several billion rows.

One of the problems I seem to have is that I can't limit the inner queries (if using them), and joins also don't appear to work.

What I wonder is mostly: can this be efficiently solved using a SQL solution, or would this lean more towards one of the NoSQL (for example, a graph database) solutions?

—
EDIT:

Queries are built up using a loop that concatenates subqueries in the following manner:

For a single table (e.g. "sessions that have a profile view after timestamp x"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `profile_views`
    WHERE
        `timestamp` > x
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

Two tables (e.g. "Sessions that have a profile view and login"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `logins`
    WHERE
        `timestamp` >= x
    AND
        `session_id` IN (
        SELECT
            `session_id`
        FROM
            `profile_views`
        WHERE
            `timestamp` >= x
    )
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

I'm looking into joins, but at the moment they appear to return different results, for example something like the following:

SELECT DISTINCT
    `A`.`session_id`
FROM
    `logins` `A`
INNER JOIN
    `profile_views` `B`
ON
    `B`.`session_id` = `A`.`session_id`
WHERE
    `A`.`timestamp` > x
ORDER BY
    `session_id` DESC
LIMIT 100;

Best Answer

Your query using joins looks ok but it will help speed up the queries if you convert the timestamp_idx key to include session_id. This way the database engine won't have to to another/second lookup/sort and on tables with millions of rows, it will have significant improvement in response times.

ALTER TABLE <table name> ADD KEY ix_covering(`timestamp`, `session_id`,`request_id`);

Drop both indexes on all tables AFTER you have tested that the new index as per above does improve the performance.

Also, instead of

SELECT DISTINCT
    `A`.`session_id`

try

SELECT `A`.`session_id`
FROM `logins` `A`
INNER JOIN `profile_views` `B` ON `B`.`session_id` = `A`.`session_id`
WHERE `A`.`timestamp` > x
GROUP BY `A`.`session_id`
ORDER BY `session_id` DESC
LIMIT 100;
Related Question