How to optimize SQL query with two where clauses?

My query is something like this

SELECT * FROM tbl1
JOIN tbl2 ON something = something
WHERE 1 AND (tbl2.date = '$date' OR ('$date' BETWEEN tbl1.planA AND tbl1.planB ))

When I run this query, it is considerably slower than for example this query

SELECT * FROM tbl1
JOIN tbl2 ON something = something
WHERE 1 AND ('$date' BETWEEN tbl1.planA AND tbl1.planB )

or

SELECT * FROM tbl1
JOIN tbl2 ON something = something
WHERE 1 AND tbl2.date = '$date'

In localhost, the f开发者_如何学编程irst query takes about 0.7 second, the second query about 0.012 second and the third one 0.008 second.

My question is how do you optimize this? If currently I have 1000 rows in my tables and it takes 0.7 second to display the first query, it will take 7 seconds if I have 10.000 rows right? That's a massive slow down compared to second query (0.12 second) and third (0.08).

I've tried adding indexes, but the result is no different.

Thanks

Edit : This application will only work locally, so no need to worry about the speed over the web.

Sorry, I didn't include the EXPLAIN because my real query are much more complicated (about 5 joins). But the joins (I think) don't really matter, cos I've tried omitting them and still get approximately the same result as above.

The date belongs to tbl1, planA and planB belongs to tbl2. I've tried adding indexes to tbl1.date, tbl2.planA and tbl2.planB but the result is insignificant.

By schema do you mean MyISAM or InnoDB? It's MyISAM.

Okay, I'll just post my query straight away. Hopefully it's not that confusing.

SELECT *
FROM tb_joborder jo
LEFT JOIN tb_project p ON jo.project_id = p.project_id
LEFT JOIN tb_customer c ON p.customer_id = c.customer_id
LEFT JOIN tb_dispatch d ON jo.joborder_id = d.joborder_id
LEFT JOIN tb_joborderitem ji ON jo.joborder_id = ji.joborder_id
LEFT JOIN tb_mix m ON ji.mix_id = m.mix_id
WHERE dispatch_date = '2011-01-11'
OR '2011-01-11'
BETWEEN planA
AND planB
GROUP BY jo.joborder_id
ORDER BY customer_name ASC 

And the describe output

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  jo  ALL     NULL    NULL    NULL    NULL    453     Using temporary; Using filesort
1   SIMPLE  p   eq_ref  PRIMARY     PRIMARY     4   db_dexada.jo.project_id     1    
1   SIMPLE  c   eq_ref  PRIMARY     PRIMARY     4   db_dexada.p.customer_id     1    
1   SIMPLE  d   ALL     NULL    NULL    NULL    NULL    2048    Using where
1   SIMPLE  ji  ALL     NULL    NULL    NULL    NULL    455      
1   SIMPLE  m   eq_ref  PRIMARY     PRIMARY     4   db_dexada.ji.mix_id     1    

---

You can just use UNION to merge results of 2nd and 3d queries.

More about UNION.

---

First thing that comes to mind is to union the two:

SELECT * FROM tbl1
JOIN tbl2 ON something = something
WHERE 1 AND ('$date' BETWEEN planA AND planB )

UNION ALL

SELECT * FROM tbl1
JOIN tbl2 ON something = something
WHERE 1 AND date = '$date'

You have provided too little to make optimizations. We don't know anything about your data structures.

---

Even if most slow queries are usually due to the query itself or index setup of the used tables, you can try to find out where your bottleneck is with using the MySQL Query Profiler, too. It has been implemented into MySQL since Version 5.0.37.

Before you start your query, activate the profiler with this statement:

 mysql> set profiling=1;

Now execute your long query.

With

mysql> show profiles;

you can now find out what internal number (query number) your long query has.

If you now execute the following query, you'll get alot of details about what took how long:

mysql> show profile for query (insert query number here);
(example output)
+--------------------+------------+
| Status             | Duration   |
+--------------------+------------+
| (initialization)   | 0.00005000 |
| Opening tables     | 0.00006000 |
| System lock        | 0.00000500 |
| Table lock         | 0.00001200 |
| init               | 0.00002500 |
| optimizing         | 0.00001000 |
| statistics         | 0.00009200 |
| preparing          | 0.00003700 |
| executing          | 0.00000400 |
| Sending data       | 0.00066600 |
| end                | 0.00000700 |
| query end          | 0.00000400 |
| freeing items      | 0.00001800 |
| closing tables     | 0.00000400 |
| logging slow query | 0.00000500 |
+--------------------+------------+

This is a more general, administrative approach, but can help narrow down or even find out the cause for slow queries very nice.

A good tutorial on how to use the MySQL Query Profiler can be found here in the MySQL articles.