MySQL and making subquery more efficient

Question

I'm using MySQL and I have a query. There is also a subquery.

SELECT * FROM rg, list, status
WHERE (
  (rg.required_status_id IS NULL AND rg.incorrect_status_id IS NULL) || 
  (status.season_id = rg.required_status_id AND status.user_id = list.user_id) || 

  (rg.incorrect_status_id IS NOT NULL AND 
    list.user_id NOT IN (SELECT user_id FROM status WHERE user_id = list.user_id AND season_id = rg.incorrect_status_id)
  )
)

The problem is the following part of the code:

(rg.incorrect_status_id IS NOT NULL AND 
  list.user_id NOT IN (SELECT user_id FROM status WHERE user_id = list.user_id AND season_id = rg.incorrect_status_id)
)

How could I check if the table "status" has a row where user_id is same as list.user_id and season_id is same as rg.incorrect_status_id?

Update

Here is my current code, but it does not work at all. I do not know what to do.

SELECT * FROM rg, list, status
  LEFT JOIN status AS stat
    INNER JOIN rg AS rglist
    ON rglist.incorrect_status_id = stat.season_id
  ON stat.season_id = rglist.incorrect_status_id

  WHERE (
    (rg.required_status_id IS NULL AND rg.incorrect_status_id IS NULL) || 
    (status.season_id = rg.required_status_id AND status.user_id = list.user_id) || 

    (rg.incorrect_status_id IS NOT NULL AND stat.user_id IS NULL)
  )
)

Update 2

I modified the names, but the basic idea is same.

FROM sarjojen_rglistat, sarjojen_rglistojen_osakilpailut, kilpailukausien_kilpailut, sarjojen_osakilpailuiden_rgpisteet
, sarjojen_kilpailukaudet, sarjojen_kilpailukausien_kilpailusysteemit
/* , kayttajien_ilmoittautumiset */
/* , sarjojen_kilpailukausien_pelaajastatukset */

LEFT OUTER JOIN sarjojen_kilpailukausien_pelaajastatukset
ON sarjojen_kilpailukausien_pelaajastatukset.sarjan_kilpailukausi_id = sarjojen_rglistat.vaadittu_pelaajastatus_id

LEFT OUTER JOIN kayttajien_ilmoittautumiset
ON kayttajien_ilmoittautumiset.kayttaja_id = sarjojen_kilpailukausien_pelaajastatukset.kayttaja_id

Now this says:

Column not found: 1054 Unknown column 'sarjojen_rglistat.vaadittu_pelaajastatus_id' in 'on clause'

Why is that so?

I have a table called "sarjojen_rglistat" and there is a column "vaadittu_pelaajastatus_id".

Answer 1

1) Simpler queries are easier for the query engine to interpret and produce an efficient plan.

If you pay careful attention to the following part of your query, you may realise something a little "weird" is going. This is a clue the approach is perhaps a little too complicated.

...(
list.user_id NOT IN (
    SELECT  user_id
    FROM    status
            /* Note the sub-query cannot ever return a user_id different
               to the one checked with "NOT IN" above */
    WHERE   user_id = list.user_id
        AND season_id = rg.incorrect_status_id)
)

The query filtering where list.user_id is not in a result set that cannot contain user_id's other than list.user_id. Of course the sub-query could return zero results. So basically it boils down to a simple existence check.

So for a start, you should rather write:

...(
NOT EXISTS (
    SELECT  *
    FROM    status
    WHERE   user_id = list.user_id
        AND season_id = rg.incorrect_status_id)
)

2) Be clear about your "what joins the tables together" (this refers back to 1 as well).

Your query selects from 3 tables without specifying any join conditions:

FROM rg, list, status

This would result in a cross join producing a result set that is a permutation combination of all possible row matches. If your WHERE clause were simple, the query engine might be able to implicitly promote certain filter conditions into join conditions, but that's not the case. So even if for example you have a very small number of rows in each table:

status   20
rg       100
list     1000

Your intermediate result set (before WHERE is applied),
would need 1000 * 100 * 20 = 2000000 rows!

It helps tremendously to make it clear with join conditions how the rows of each table are intended to match up. Not only does it make the query easier to read and understand, but it also helps avoid overlooking join conditions which can be the bane of performance considerations.

Note that when specifying join conditions, some rows might not have matches and this is where knowing and understanding the different types of joins is extremely important. Particularly in your case, most of the complexity in your WHERE clause seems to come from trying resolve when rows do/do not match. See this answer for some useful information.

Your FROM/WHERE clause should probably look more like the following. (Difficult to be certain because you haven't stated your table relationships or expected input/output of your query. But it should set you on the right track.)

FROM    rg
        /* Assumes rg rows form the base of the query, and not to have
           some rg rows excluded due to non-matches in list or status. */
        LEFT OUTER JOIN status ON
            status.season_id = rg.required_status_id
        LEFT OUTER JOIN list ON
            status.user_id = list.user_id
WHERE   rg.incorrect_status_id IS NULL
    /* As Barmar commented, it may also be useful to break this
       OR condition out as a separate query UNION to the above.  */
    OR  (
            rg.incorrect_status_id IS NOT NULL
        AND NOT EXISTS (
            SELECT  *
            FROM    status
            WHERE   user_id = list.user_id
                AND season_id = rg.incorrect_status_id)
        )

Note that this query is very clear about the distinction between how the tables are joined, and what is used to filter the joined result set.

3) Finally and very importantly, even the best queries are of little benefit without the correct indexes!

A good query with bad indexes (or conversely a bad query with good indexes) is going to be inefficient either way. Computers are fast enough that you might not notice on small databases, but you do experiment with candidate indexes to find the best combination for your data and workload.

In the above query you likely need indexes on the following. (Some may already be covered by Primary Key constraints.)

status.season_id
status.user_id
list.user_id
rg.required_status_id
rg.incorrect_status_id

Answer 2

Use a UNION of subqueries that handle the 3 cases that you're combining with OR. You can then use explicit JOIN in each subquery to make it clear how the tables are related to each other (or not related at all when you're doing a full cross-product, as is the case when rg.required_status_id IS NULL AND rg.incorrect_status_id IS NULL).

SELECT rg.*, list.*, status.*
FROM rg
CROSS JOIN list
CROSS JOIN status
WHERE rg.required_status_id IS NULL AND rg.incorrect_status_id IS NULL

UNION ALL

SELECT rg.*, list.*, status.*
FROM rg
JOIN status ON rg.required_status_id = status.season_id
JOIN list ON status.user_id = list.user_id

UNION ALL

SELECT rg.*, list.*, status.*
FROM rg
CROSS JOIN list
LEFT JOIN status ON status.user_id = list.user_id AND status.season_id = rg.required_status_id
WHERE rg.incorrect_status_id IS NOT NULL AND status.season_id IS NULL