mysql query optimization: select with counted subquery extremely slow

Question

I have the following tables:

mysql> show create table rsspodcastitems \G
*************************** 1. row ***************************
       Table: rsspodcastitems
Create Table: CREATE TABLE `rsspodcastitems` (
  `id` char(20) NOT NULL,
  `description` mediumtext,
  `duration` int(11) default NULL,
  `enclosure` mediumtext NOT NULL,
  `guid` varchar(300) NOT NULL,
  `indexed` datetime NOT NULL,
  `published` datetime default NULL,
  `subtitle` varchar(255) default NULL,
  `summary` mediumtext,
  `title` varchar(255) NOT NULL,
  `podcast_id` char(20) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `podcast_id` (`podcast_id`,`guid`),
  UNIQUE KEY `UKfb6nlyxvxf3i2ibwd8jx6k025` (`podcast_id`,`guid`),
  KEY `IDXkcqf7wi47t3epqxlh34538k7c` (`indexed`),
  KEY `IDXt2ofice5w51uun6w80g8ou7hc` (`podcast_id`,`published`),
  KEY `IDXfb6nlyxvxf3i2ibwd8jx6k025` (`podcast_id`,`guid`),
  KEY `published` (`published`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `summary` (`summary`),
  FULLTEXT KEY `subtitle` (`subtitle`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table station_cache \G
*************************** 1. row ***************************
       Table: station_cache
Create Table: CREATE TABLE `station_cache` (
  `Station_id` char(36) NOT NULL,
  `item_id` char(20) NOT NULL,
  `item_type` int(11) NOT NULL,
  `podcast_id` char(20) NOT NULL,
  `published` datetime NOT NULL,
  KEY `Station_id` (`Station_id`,`published`),
  KEY `IDX12n81jv8irarbtp8h2hl6k4q3` (`Station_id`,`published`),
  KEY `item_id` (`item_id`,`item_type`),
  KEY `IDXqw9yqpavo9fcduereqqij4c80` (`item_id`,`item_type`),
  KEY `podcast_id` (`podcast_id`,`published`),
  KEY `IDXkp2ehbpmu41u1vhwt7qdl2fuf` (`podcast_id`,`published`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The "item_id" column of the second refers to the "id" column of the former (there isn't a foreign key between the two because the relationship is polymorphic, i.e. the second table may have references to entities that aren't in the first but in other tables that are similar but distinct).

I'm trying to get a query that lists the most recent items in the first table that do not have any corresponding items in the second. The highest performing query I've found so far is:

select i.*, 
       (select count(station_id) 
         from station_cache 
          where item_id =  i.id) as stations 
 from rsspodcastitems i 
  having stations = 0 
   order by published desc

I've also considered using a where not exists (...) subquery to perform the restriction, but this was actually slower than the one I have above. But this is still taking a substantial length of time to complete. MySQL's query plan doesn't seem to be using the available indices:

+----+--------------------+---------------+------+---------------+------+---------+------+--------+----------------+
| id | select_type        | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+--------------------+---------------+------+---------------+------+---------+------+--------+----------------+
|  1 | PRIMARY            | i             | ALL  | NULL          | NULL | NULL    | NULL | 106978 | Using filesort |
|  2 | DEPENDENT SUBQUERY | station_cache | ALL  | NULL          | NULL | NULL    | NULL |  44227 | Using where    |
+----+--------------------+---------------+------+---------------+------+---------+------+--------+----------------+

Note that neither portion of the query is using a key, whereas it ought to be able to use KEY published (published) from the primary table and KEY item_id (item_id,item_type) for the subquery.

Any suggestions how I can get an appropriate result without waiting for several minutes?

Answer 1

My eventual solution was to delete the full text indices and use an externally generated index table (produced by iterating over the words in the text, filtering stop words, and applying a stemming algorithm) to allow searching. I don't know why the full text indices were causing performance problems, but they seemed to slow down every query that touched the table even if they weren't used.

Answer 2

I would expect the fastest query to be:

select i.*
from rsspodcastitems i 
where not exists (select 1
                  from station_cache sc
                  where sc.item_id = i.id
                 )
order by published desc;

This would take advantage of an index on station_cache(item_id) and perhaps rsspodcastitems(published, id).

Your query could be faster, if your query returns a significant number of rows. Your phrasing of the query allows the index on rsspodcastitems(published) to avoid the file sort. If you remove the group by, the exists version should be faster.

I should note that I like your use of the having clause. When faced with this in the past, I have used a subquery:

select i.*, 
       (select count(station_id) 
         from station_cache 
          where item_id =  i.id) as stations 
from (select i.*
      from rsspodcastitems i 
      order by published desc
     ) i
where not exists (select 1
                  from station_cache sc
                  where sc.item_id = i.id
                 );

This allows one index for sorting.

I prefer a slight variation on your method:

select i.*, 
       (exists (select 1 
                from station_cache sc
                where sc.item_id = i.id
               )
       ) as has_station 
from rsspodcastitems i 
having has_station = 0 
order by published desc;

This should be slightly faster than the version with count().

Answer 3

You might want to detect and remove redundant indexes from your tables. Reviewing your CREATE TABLE information for both tables with help you discover several, including podcast_id,guid and Station_id,published, item_id,item_type and podcast_id,published there may be more.