Search code examples
mysqlmariadbsql-optimization

mySQL: Query just keeps loading

I want to join 4 tables together. One table comes from database A, while 3 other tables come from database B. When I run the following query, the computer just keeps loading, with no error or result in sight. My query is: 

SELECT tableA.ts, tableA.voltage, tableB.Day0, tableC.Day0, tableD.Day0 FROM databaseA.tableA \
    -> INNER JOIN databaseB.tableB ON tableB.ts \
    -> INNER JOIN databaseB.tableC ON tableC.ts \
    -> INNER JOIN databaseB.tableD ON tableD.ts\
    -> BETWEEN tableA.ts - INTERVAL 50 SECOND AND tableA.ts + INTERVAL 50 SECOND

Since I am quite new to SQL, I wanted to ask if my query design make sense and if there are any problems with it that may be causing problems with the query i.e. (non-stop loading).

The complexity of the current query comes from needing to join multiple tables together from multiple databases. The commonality where these tables need to join will be at the ts (timestamp). I've added BETWEEN tableA.ts - INTERVAL 50 SECOND AND tableA.ts + INTERVAL 50 SECOND in the query design above, because the timestamp between the tables may be off by a couple of seconds.

Solution

  • This is a bit long for a comment.

    Your ON conditions are not correct. You are doing Cartesian products between the tables.

    ON conditions should be boolean comparisons between two (or possibly more) tables. So, your expressions should look like:

    ON tableB.ts = . . .

    (Equality would be the normal operator.)

    MySQL will convert other types to booleans. For numbers, 0 is considered "false" and everything else is "true" (NULLs are NULLs).

    So:

    ON tableB.ts

    is acceptable. But it is really just testing:

    ON tableB.ts <> 0