Mysql – Join on with modified ON

MySQLmysql-5.5optimization

I am trying to create table with summary of few other tables. All solutions i came up with are very slow and clearly not optimal. Initially I tried with multitude of subqueries but that was very slow. Now I have JOIN but I am not fully grasping which join I should make.

2 tables are like following

Table 1: recent_items

ID, timestamp, Text1...TextN

Table 2: insider_trades

ID, timestamp, Text1...TextM

Where all columns but ID are not related at all and # of cols is not the same. And no IDs are not foreign keys, long story short DB has different collations and i wasn't able to unite them yet. (I didnt create the DB itself)

My best attempt with join so far is:

SELECT 
    DATE(recent_items.time),
    SUM(CASE WHEN crit1 = "twitter" THEN 1 ELSE 0 END) Social,  # one that works
FROM 
    recent_items 
JOIN
    (SELECT crit2, crit3, DATE(filedate) AS fdate
     FROM insider_trades 
    ) AS InInfo ON DATE(recent_items.time) = DATE(InInfo.fdate) 
GROUP BY 
    DATE(recent_items .time)

I need to do 2 things

Sum from table 1 (done)
Sum from table 2, irrelevant of ID but based on date specified by
SELECT date(table1.time) . Ex. total value of crit2 on certain
date or something like SUM(crit2 where crit3 = "A" and date(recent_items.time) = date(InInfo.fdate))

For 2 I tried passing user defined variable but with not much luck either

Best Answer

This should work if you want to join two tables by day and do counts on different conditions of the columns.

SELECT *
FROM (
SELECT 
    SUM(CASE WHEN crit1 = "AAA" THEN 1 ELSE 0 END) as TheAs,
    SUM(CASE WHEN crit1 = "BBB" THEN 1 ELSE 0 END) as TheBs,
    SUM(CASE WHEN crit3 = "CCC" THEN 1 ELSE 0 END) as TheCs,
    date(time) someDay
    FROM recent_items
    GROUP BY date(time)
) as recitems
JOIN (
    SELECT 
    SUM(CASE WHEN crit1 = "AAA" THEN 1 ELSE 0 END) as TheAs,
    SUM(CASE WHEN crit1 = "BBB" THEN 1 ELSE 0 END) as TheBs,
    SUM(CASE WHEN crit3 = "CCC" THEN 1 ELSE 0 END) as TheCs,
    date(time) someDay
    FROM insider_trades
    GROUP BY [timestamp]
) as InInfo ON recitems.someDay = InInfo.someDay

This may be slow depending on the size of the data that is being queried. Limiting the number of days you are trying to get data for by adding where clauses to the inner queries will help.

Related Solutions

Mysql query is very slow

SELECT ... FROM ...
JOIN ( SELECT ... ) ON ...
JOIN ( SELECT ... ) ON ...

has an inherent optimization problem. There will be no indexes on those subqueries. If they have, say, 1000 rows each, then it will take a million (1000*1000) operations to do the JOINs.

If these truly need to be LEFT JOINs (not plain JOINs), then the optimizer cannot even start with one of those subqueries, thereby avoiding one of the 1000x multipliers.

One possible solution is to do CREATE TEMPORARY TABLE mf ( PRIMARY KEY (UserID) ) SELECT ...; -- where the SELECT is the first subquery. Ditto for usar.

Note that I am suggesting creating the necessary index as the temp table is built.

MySQL CTE vs Join – Which to Use?

Without table structure, it's not easy to write code but here is a simplified query:

SELECT 
    c.*, i.*,
    r.max_date
FROM 
    customer AS c
  LEFT JOIN                                        -- or just JOIN 
    ( SELECT customernumber, 
             MAX(revenue_date) AS max_date
      FROM revenue
      GROUP BY customernumber
    ) AS r
      ON  r.customernumber = c.customernumber
  LEFT JOIN                                        -- or just JOIN
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.max_date ;

The above is standard SQL and will work in MySQL as in all other DBMS. For more complex situations though, where LATERAL or similar syntax can be used to provide more elegant (or more efficient) SQL, MySQL is not helping. It lacks many of the standard SQL syntax (like common table expressions, table value constructors, window functions, LATERAL, CHECK constraints and probably some other I forget).

If you want to simulate (SQL-Server's) OUTER APPLY, (the ISO/ANSI) LATERAL or (DB2's) TABLE syntax, it becomes (bad news) quite complicated but (good news) usually quite efficient with proper indexing. This is mainly useful when you have a small "base" table (the customer in this case) or you want to limit the query to only a few customers with a WHERE clause. While the query above with the GROUP BY will have to find the MAX(revenue_date) for all customers (and that means a full table or a full index scan in revenue), the following one will only find the max for the few selected customers (so a few index seeks):

SELECT 
    c.*, i.*,
    r.revenue_date AS max_date
FROM 
    customer AS c
  LEFT JOIN
    revenue AS r
      ON  r.customernumber = .customernumber
      AND r.PK = 
          ( SELECT ri.PK
            FROM revenue AS ri
            WHERE ri.customernumber = c.customernumber
            ORDER BY ri.revenue_date DESC
              LIMIT 1
          ) 
  LEFT JOIN                                        
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.revenue_date 
WHERE
    <some conditions on the customer table> ;

An advantage is that you can have any column of the revenue table in the final SELECT list.
There are several improvements/variations, like if the (customernumber, revenue_date) is unique, you can use the revenue_date instead of the primary key for the join.
If you don't want one row only but several (a TOP n query), it gets quite complicated but can still be done.

Best Answer

Related Solutions

Mysql query is very slow

MySQL CTE vs Join – Which to Use?

Related Question