Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
4  
Creating "all possible indices" is rarely a good idea. If you have come anywhere near creating all the possible indices, you have almost certainly made every query slower. Could you also add the EXPLAIN EXTENDED of that query? –  Ladadadada Mar 2 '12 at 23:17
add comment

2 Answers

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.

share|improve this answer
    
Sure I know what are indices standing for, and I've try literally every index I can imagine. I've even play with field order in complex indices and also have try to give an index hints to the engine. Seems that mysql fails on a JOIN when it performed with BETWEEN statement. –  Kondybas Mar 3 '12 at 0:01
    
So if you are acclimated to MySQL, use EXPLAIN EXTENDED as stated by Ladadadada. This gives sometimes ideas about what is wrong. If you think this is BETWEEN, just replace it with a "<" test and a ">" test. And, in some rare cases, MySQL don't order the table correctly when doing joins: use STRAIGHT_JOIN and try both join order. Hope this will be of any help. Note: I didn't saw "all possible indices has been created", sorry for the delay. –  Gregory MOUSSAT Mar 3 '12 at 0:24
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.