I'm trying to retrieve the "most viewed articles" and
the number of times viewed,
Any ideas for optimizing this?
I'm a little confused about the EXPLAIN results since
the second *should* be faster according to my
interpretation of the manual
A couple explanations
stats.vdate -> unix timestamp of the "stat" insert
stats.action -> either a 1,2,3
1 = article view
2 = article printed
3 = article emailed
zones are like categories - basically an adjacency
model hierarchy
simplified zones table
CREATE TABLE zones (
id smallint(4) unsigned NOT NULL auto_increment,
parent_id smallint(4) unsigned NOT NULL default
'0',
name varchar(60) NOT NULL default '',
KEY parent_id (parent_id,id)
) TYPE=MyISAM;
-------------------------------------------------------
The query
-------------------------------------------------------
SELECT
count(*) AS cnt, articles.id, articles.title,
articles.pub_date, z1.urlname as zone_urlname,
z2.urlname as zone_parent_urlname |
FROM
article_stats AS stats
INNER JOIN
articles ON (stats.article_id=articles.id)
INNER JOIN
zones AS z1 ON (stats.zone_id=z1.id)
INNER JOIN
zones AS z2 ON (z1.parent_id=z2.id)
WHERE
stats.vdate BETWEEN 1078300800 AND 1078511428 AND
stats.action = 1 AND
stats.site_id = 1
GROUP BY stats.article_id
ORDER BY cnt DESC
LIMIT 10;
-------------------------------------------------------
This query executes in @ 1.7 seconds
-------------------------------------------------------
-------------------------------------------------------
results for EXPLAIN
-------------------------------------------------------
articles | ALL | PRIMARY | NULL | NULL | NULL | 25818
| where used; Using temporary; Using filesort |
stats | ref | vdate,article_id,v_a | article_id | 4 |
articles.id | 15 | where used |
z1 | eq_ref | PRIMARY,parent_id | PRIMARY | 2 |
stats.zone_id | 1 | where used |
z2 | eq_ref | PRIMARY | PRIMARY | 2 | z1.parent_id | 1
|
-------------------------------------------------------
v_a is a multi column index of vdate and article_id
if this index is used the query executes in @ 2.6
seconds
-------------------------------------------------------
Here are results for EXPLAIN (same query with USE
INDEX (v_a))
-------------------------------------------------------
table | type | possible_keys | key | key_len | ref |
rows | Extra |
stats | range | vdate,article_id,v_a | v_a | 4 | NULL
| 282217 | where used; Using temporary; Using filesort
|
articles | eq_ref | PRIMARY | PRIMARY | 4 |
stats.article_id | 1 | where used |
z1 | eq_ref | PRIMARY,parent_id | PRIMARY | 2 |
stats.zone_id | 1 | where used |
z2 | eq_ref | PRIMARY | PRIMARY | 2 | z1.parent_id | 1
|
-------------------------------------------------------
Thanks much,
olinux
__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you’re looking for faster
http://search.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
