Weird MySQL query plan: why is this query using temporary & filesort? How to optimize it?

Question

I have a query:

SELECT *
FROM amp_ads,amp_c,amp_c_countries    
WHERE 
(amp_c.zone = '24' OR amp_c.zone = '25') AND 
amp_ads.ad_complete = '1' AND 
amp_ads.ad_type = '17' AND 
amp_ads.accept = '1' AND 
amp_ads.en_w = '1' AND 
amp_c.en_u = '1' AND 
amp_c.en_w = '1' AND 
(amp_c.i_nu>'0' OR amp_c.c_nu>'0' OR amp_c.d_valid_by>'1299341823' OR amp_c.unlimit='1') AND 
(amp_c.i_d_max='0' OR amp_c.i_d_nu>'0') AND 
(amp_c.c_d_max='0' OR  amp_c.c_d_nu>'0') AND 
amp_c.t1<'1299341823' AND 
amp_c.t2>'1299341823' AND 
amp_c.d7 = '1'  AND 
(amp_c.some_countr = '0' OR (amp_c_countries.country = 'ES' AND amp_c.n = amp_c_countries.ad AND amp_c.camp = amp_c_countries.c))  AND 
amp_c.n = amp_ads.n AND 
amp_ads.def = 0       
ORDER BY amp_c.price_c desc LIMIT 1 

(It's actually not SELECT *, but I simplified the SELECT clause to make it less messy.)

The output of EXPLAIN of the above query is:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: amp_c
         type: ref
possible_keys: work,n_index,zone_price
          key: zone_price
      key_len: 4
          ref: const
         rows: 79
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: amp_ads
         type: eq_ref
possible_keys: n,work
          key: n
      key_len: 4
          ref: advertis_admpro.amp_c.n
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: amp_c_countries
         type: index
possible_keys: work
          key: work
      key_len: 12
          ref: NULL
         rows: 4083
        Extra: Using where; Using index; Using join buffer

1) Why is the 1st table Using temporary and Using filesort? EXPLAIN show that it is using the index zone_price, which is made up of 2 columns: (zone, price_c). So after the index is used to select rows based on the zone value, all the resulting rows are in the order of price_c. And since the query is ORDER BY price_c, there should be no need for Using temporary and Using filesort at all. What am I missing?

2) For the 3rd table, it is supposed to be using the index work. But yet ref is NULL. What does that mean? work is made up of the columns (ad,c,country). So when rows are selected from amp_c_countries using the WHERE clause (amp_c_countries.country = 'ES' AND amp_c.n = amp_c_countries.ad AND amp_c.camp = amp_c_countries.c), shouldn't it be just a simple index lookup? The rows value in EXPLAIN is 4083, amp_c_countries has 4113 rows according to SHOW TABLE STATUS. Does that mean MySQL is doing a full index scan instead of a lookup?

3) Any ideas on how to fix the above 2 problems? amp_ads contains TEXT columns, so a lot of disk temp tables are being created:

| Created_tmp_disk_tables               | 906952      |
| Created_tmp_files                     | 11          |
| Created_tmp_tables                    | 912227      |

show processlist also shows many processes are in the state of Copying to tmp table.

Thanks. Appreciate your help.

EDIT:

Outputs of SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE `advertis_admpro`.`amp_c`\G
*************************** 1. row ***************************
       Table: amp_c
Create Table: CREATE TABLE `amp_c` (
  `n` int(10) unsigned NOT NULL DEFAULT '0',
  `camp` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `zone` int(11) NOT NULL DEFAULT '0',
  `javascript` tinyint(1) NOT NULL DEFAULT '0',
  `banner_target` varchar(50) NOT NULL DEFAULT '',
  `accept` tinyint(1) NOT NULL DEFAULT '0',
  `en_u` tinyint(1) NOT NULL DEFAULT '0',
  `en_w` tinyint(1) NOT NULL DEFAULT '0',
  `i_got` int(10) unsigned NOT NULL DEFAULT '0',
  `c_got` int(10) unsigned NOT NULL DEFAULT '0',
  `r` double(4,2) unsigned NOT NULL DEFAULT '0.00',
  `price_i` double(10,6) unsigned NOT NULL,
  `price_c` double(10,3) unsigned NOT NULL,
  `i_nu` int(11) NOT NULL DEFAULT '0',
  `c_nu` int(11) NOT NULL DEFAULT '0',
  `unlimit` tinyint(1) NOT NULL DEFAULT '0',
  `d_total` int(10) unsigned NOT NULL DEFAULT '0',
  `d_valid_by` int(10) unsigned NOT NULL DEFAULT '0',
  `t1` int(10) unsigned NOT NULL DEFAULT '0',
  `t2` int(10) unsigned NOT NULL DEFAULT '0',
  `d1` tinyint(1) NOT NULL DEFAULT '0',
  `d2` tinyint(1) NOT NULL DEFAULT '0',
  `d3` tinyint(1) NOT NULL DEFAULT '0',
  `d4` tinyint(1) NOT NULL DEFAULT '0',
  `d5` tinyint(1) NOT NULL DEFAULT '0',
  `d6` tinyint(1) NOT NULL DEFAULT '0',
  `d7` tinyint(1) NOT NULL DEFAULT '0',
  `tz1` tinyint(1) NOT NULL DEFAULT '0',
  `tz2` tinyint(1) NOT NULL DEFAULT '0',
  `tz3` tinyint(1) NOT NULL DEFAULT '0',
  `tz4` tinyint(1) NOT NULL DEFAULT '0',
  `tz5` tinyint(1) NOT NULL DEFAULT '0',
  `some_countr` tinyint(1) NOT NULL DEFAULT '0',
  `i_d_max` int(10) unsigned NOT NULL DEFAULT '0',
  `c_d_max` int(10) unsigned NOT NULL DEFAULT '0',
  `i_d_nu` int(10) unsigned NOT NULL DEFAULT '0',
  `c_d_nu` int(10) unsigned NOT NULL DEFAULT '0',
  `last` int(10) unsigned NOT NULL DEFAULT '0',
  `user` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(15) NOT NULL DEFAULT '',
  `emailed` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `work` (`en_u`,`en_w`,`i_nu`,`c_nu`,`d_valid_by`,`unlimit`,`i_d_max`,`c_d_max`,`i_d_nu`,`c_d_nu`,`t1`,`t2`,`n`),
  KEY `n_index` (`n`,`camp`),
  KEY `zone_price` (`zone`,`price_c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `advertis_admpro`.`amp_ads`\G
*************************** 1. row ***************************
       Table: amp_ads
Create Table: CREATE TABLE `amp_ads` (
  `n` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `ad_type` int(10) unsigned NOT NULL DEFAULT '0',
  `accept` tinyint(1) NOT NULL DEFAULT '0',
  `en_w` tinyint(1) NOT NULL DEFAULT '0',
  `weight` tinyint(1) NOT NULL DEFAULT '0',
  `w` smallint(5) unsigned NOT NULL DEFAULT '0',
  `h` smallint(5) unsigned NOT NULL DEFAULT '0',
  `norepeat` int(10) unsigned NOT NULL DEFAULT '0',
  `campaigns` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `zones` text NOT NULL,
  `keywords` text NOT NULL,
  `banner` varchar(255) NOT NULL DEFAULT '',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alt` varchar(255) NOT NULL DEFAULT '',
  `raw` text NOT NULL,
  `kind` varchar(40) NOT NULL DEFAULT '',
  `javascript` tinyint(1) NOT NULL DEFAULT '0',
  `ad_complete` tinyint(1) NOT NULL DEFAULT '0',
  `url1` text NOT NULL,
  `url2` text NOT NULL,
  `url3` text NOT NULL,
  `text1` text NOT NULL,
  `text2` text NOT NULL,
  `text3` text NOT NULL,
  `text4` text NOT NULL,
  `text5` text NOT NULL,
  `text6` text NOT NULL,
  `text7` text NOT NULL,
  `text8` text NOT NULL,
  `text9` text NOT NULL,
  `text10` text NOT NULL,
  `picture1` varchar(255) NOT NULL DEFAULT '',
  `picture2` varchar(255) NOT NULL DEFAULT '',
  `picture3` varchar(255) NOT NULL DEFAULT '',
  `picture4` varchar(255) NOT NULL DEFAULT '',
  `picture5` varchar(255) NOT NULL DEFAULT '',
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `user` int(11) NOT NULL DEFAULT '0',
  `username` varchar(15) NOT NULL DEFAULT '',
  `preview` text NOT NULL,
  `def` tinyint(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `n` (`n`),
  KEY `work` (`ad_type`,`accept`,`en_w`,`norepeat`,`ad_complete`,`def`)
) ENGINE=InnoDB AUTO_INCREMENT=1532 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `advertis_admpro`.`amp_c_countries`\G
*************************** 1. row ***************************
       Table: amp_c_countries
Create Table: CREATE TABLE `amp_c_countries` (
  `ad` int(10) unsigned NOT NULL DEFAULT '0',
  `c` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `country` varchar(5) NOT NULL DEFAULT '',
  KEY `work` (`ad`,`c`,`country`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Answer 1

In order to prevent the need for sorting, when sorting according to the index's second part, the first part must remain constant.

In your case, the condition for the first part is amp_c.zone = '24' OR amp_c.zone = '25', which may not be good enough.

Try changing the condition to amp_c.zone = '24' only, see if that changes the explain (Obviously you will not get all the results you need, but do it to validate my guess)...

If it works and the explain isn't showing using filesort again, you have 2 options:

1. Sort by all parts of the index: ORDER BY amp_c.zone, amp_c.price_c.
2. Have only one condition on the zone column, and union with another similar query for the second condition, something like:

---

(SELECT  ...  WHERE zone = 24 ... ORDER BY price_c)
UNION
(SELECT  ...  WHERE zone = 25 ... ORDER BY price_c)
ORDER BY price_c