Help me solve a problem with MySQL, as documented here:

http://www.writely.com/View.aspx?docid=baccf6xnntdtb

Timothy,

So I worked on this problem for you today, it was a fun question.  

I since found that I can't get paid for it, no worries.

"Because of an overwhelming response by qualified candidates, we are
temporarily not accepting additional applications. Please check back
with us again, as we likely will begin accepting applications again in
the near future."


So anyway, I've posted the results up here:

http://wickedpsyched.net/answers/

Your difficult query comes back at the right speed now.

Best regards.

Wicked Psyched,

I don't think your solution is fully thougtht out.  You tell me to
create a new compound index to solve the problem, then the execution
plan you present doesn't use the full index.  See the key_len column
below.  It is only 8, which means that only the "occurred" part of the
index is used.

mysql> explain select straight_join * from sample join device using
(device_id) where panelist_id = 37 and occurred between '2005-07-01
00:00:00' and '2005-07-01 12:00:00';
+--------+-------+------------------------------+------------------------------+---------+------------------------------+--------+--------------------------+
| table  | type  | possible_keys                | key                 
        | key_len | ref                          | rows   | Extra     
              |
+--------+-------+------------------------------+------------------------------+---------+------------------------------+--------+--------------------------+
| sample | range | occurred_device_compound_idx |
occurred_device_compound_idx |       8 | NULL                        
| 397765 | Using where; Using index |
| device | ref   | PRIMARY,dev_idx              | dev_idx             
        |       8 | const,bench.sample.device_id |      1 | Using
where              |
+--------+-------+------------------------------+------------------------------+---------+------------------------------+--------+--------------------------+
2 rows in set (0.00 sec)

I am further studying your solution and will report more later.

I think I understand why Wicked Psyched's solution runs so fast.  He
has added enough information to the compound indexes, so that the full
file scan can be done using information in the indexes.  That's an
interesting work around.

Of course, the solution I wanted was to avoid the full filescan altogether.

I talked to MySQL Network, and they told me it's a known issue, and
that they have no plans to address it.  Basically, if you join two
tables and have a constraint on one column from each table, it's going
to run slowly.  *sigh*