Thanks for the reply, very insightful! Apologies to filesort, didn't mean to tarnish his name!
Unfortunately there's was no discernible difference using the query you suggested. It took almost exactly the same amount of time as my original one. Below is the explain for your query.
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 10819 | Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 602 | tpdata.p.VP_AssetId | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+---------------------------------+
You are right, VC_Valid is most of the time set to 1, in fact in my 10,000 entry test dataset they are all 1 so it currently wont filter anything out. I've included below the information you requested. The server has 1GB of RAM and top currently reports that 477MB of that is free.
mysql> SHOW VARIABLES WHERE Variable_name = 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS WHERE NAME = 'vod_package' OR NAME ='vod_control';
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| vod_control | MyISAM | 10 | Dynamic | 10819 | 56 | 605864 | 281474976710655 | 93184 | 0 | NULL | 2010-03-26 17:15:33 | 2010-03-30 17:05:39 | NULL | utf8_unicode_ci | NULL | | |
| vod_package | MyISAM | 10 | Dynamic | 10819 | 54 | 594872 | 281474976710655 | 93184 | 0 | NULL | 2010-03-29 14:49:40 | 2010-03-30 17:05:39 | NULL | utf8_unicode_ci | NULL | | |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
Again thanks for all the help!
Mike