Home / server / Questions / 365860
Accepted
Kondybas
Asked: 2012-03-03 14:46:49 +0800 CST

Heavy load on a simple query

  • 772

Two tables exists:

maindata = id, devid, value (10M rows)
djournal = id, devid, md_id_begin, md_id_end, state (10k rows)

I want to select all from maindata for certain devid except rows having wrong state:

SELECT md.* 
  FROM maindata AS md
  LEFT JOIN djournal AS dj
    ON md.id BETWEEN dj.md_id_begin AND dj.md_id_end
    AND md.devid = dj.devid
  WHERE md.devid = 123456789
    AND dj.state <> 'idle'
  ORDER BY md.id ASC;

Given query produce exactly what I want, but sloooooow. All possible indices has been created. Sure it's easy to store state field directly in the maindata table, but it's curious why that query is so slow and is any workaround exists?

mysql

2 Answers

  1. You just have an index problem.

    You didn't published the database structure, but if you ask this question, this is because you don't know much about databases (because every decent db server can show you where the query spend its time).

    Your missing indexes are probably on md_id_begin, md_id_end as well as state. Just a guess.
    Indexing id could also be a very good idea if you didn't.

    • 0
  2. Best Answer

    Sorry for disturbance, people, no solution exists for that problem. That's not a problem at all, that's a normal sql-engine's behaviour. I've try to explain why. Let we have two sets:

    mysql> select * from Q;      mysql> select * from R;
+----+------+                +----+------+
| id | val  |                | id | val  |
+----+------+                +----+------+
|  1 | a    |                |  1 | a    |
|  2 | b    |                |  2 | b    |
|  3 | c    |                |  3 | c    |
|  4 | d    |                |  4 | d    |
|  5 | e    |                |  5 | e    |
+----+------+                +----+------+

    Let make a JOIN with no condition:

    mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R;
+------+------+
| Qval | Rval |
+------+------+
| a    | a    |
| b    | a    |
| c    | a    |
| d    | a    |
| e    | a    |
| a    | b    |
| b    | b    |
| c    | b    |
| d    | b    |
| e    | b    |
| a    | c    |
| b    | c    |
| c    | c    |
| d    | c    |
| e    | c    |
| a    | d    |
| b    | d    |
| c    | d    |
| d    | d    |
| e    | d    |
| a    | e    |
| b    | e    |
| c    | e    |
| d    | e    |
| e    | e    |
+------+------+
25 rows in set (0.00 sec)

    Let's straighten JOIN by "=" condition:

    mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val = R.val;
+------+------+
| Qval | Rval |
+------+------+
| a    | a    |
| b    | b    |
| c    | c    |
| d    | d    |
| e    | e    |
+------+------+
5 rows in set (0.00 sec)

    And when we JOIN on ">" we get:

    mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val > R.val;
+------+------+
| Qval | Rval |
+------+------+
| b    | a    |
| c    | a    |
| d    | a    |
| e    | a    |
| c    | b    |
| d    | b    |
| e    | b    |
| d    | c    |
| e    | c    |
| e    | d    |
+------+------+
10 rows in set (0.00 sec)

    Lax condition produce lax result. Complex condition reduce the resulting set, but significantly increase the amount of calculations. When we JOIN on BETWEEN or < or > we get huge temporary tables for intermediate results - with no indices, searched by filesort.

    So, joining sets by something else than "=" - is a bad idea.

    • 0