I have the following 2 tables:
CREATE TABLE media (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned default NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
`hash` varchar(255) NOT NULL,
length float(9,2) NOT NULL,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
`type` enum('video','image') default NULL,
`status`
enum('new','processing','suspended','active','deleted','failed','pending')
NOT NULL default 'new',
flags int(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `hash` (`hash`),
KEY `type` (`type`),
KEY user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and
CREATE TABLE media_views (
media_id int(20) unsigned NOT NULL,
user_id int(12) unsigned NOT NULL,
views int(20) unsigned NOT NULL,
30d int(20) unsigned NOT NULL,
7d int(20) unsigned NOT NULL,
24h int(20) unsigned NOT NULL,
site30d int(11) unsigned NOT NULL default '0',
site7d int(11) unsigned NOT NULL default '0',
site24h int(11) unsigned NOT NULL default '0',
click int(20) NOT NULL,
last_dt timestamp NOT NULL default '0000-00-00 00:00:00' on update
CURRENT_TIMESTAMP,
PRIMARY KEY (media_id),
KEY last_dt (last_dt),
KEY user_id (user_id),
KEY 7d (7d,24h)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
and the following query using them:
select
SQL_BIG_RESULT media.*, media_views.*, media.created as dt,
media_views.views + media_views.embeds as alltime_views
FROM media
JOIN media_views ON ( media.id = media_views.media_id )
where
media.status = 'active'
and media.type = 'whatever'
order by
24h DESC, media.created desc LIMIT 0, 20
each table has about 125,000 records, and the query takes about 4 seconds to
run.
When I run explain on the query, it says:
+----+-------------+-------------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows |
Extra |
+----+-------------+-------------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
| 1 | SIMPLE | media | ref | PRIMARY,type | type |
2 | const | 56518 | Using where; Using temporary; Using
filesort |
| 1 | SIMPLE | media_views | eq_ref | PRIMARY | PRIMARY |
4 | awv_free.media.id | 1
| |
+----+-------------+-------------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
When I try to add a force index (PRIMARY) after the media table to try and
make is use PRIMARY, rather than TYPE, the optimizer switches and uses no
key at all. I've tried to change the order in which the tables are selected,
but it seems to have no effect. In some scenarios it will switch and use the
media_views table, but the rows is still 125,000+ using temporary and
filesort.
how can I get this query time down?
