List:General Discussion« Previous MessageNext Message »
From:King Xaymaca Date:April 3 1999 5:22pm
Subject:Trying to optimize a join
View as plain text  
Is there any way to improve this query?
As you can see I have indexes everywhere. 
Thanks in advance.

select h.artist, h.song, h.alabel, p.uri , p.whits, h.ra_url
from hitlist.htemplate h , xaymaca.pagestats p  
where p.huid = h.luid 
and left(p.uri,22) ='http://www.hitlist.com'
and p.dhits != 0
and uri != 'http://www.hitlist.com/music/newresult.php3'
order by p.whits desc
limit 20 ;

explain returns:


+-------+--------+---------------+---------+---------+--------+------+------------+
| table | type   | possible_keys | key     | key_len | ref    | rows |
Extra      |
+-------+--------+---------------+---------+---------+--------+------+------------+
| p     | ALL    | huid          | NULL    |    NULL | NULL   | 4017 |
where used |
| h     | eq_ref | PRIMARY       | PRIMARY |       3 | p.huid |    1
|            |
+-------+--------+---------------+---------+---------+--------+------+------------+



2 rows in set (0.00 sec)

table stucture is :
----------------------------

# Server version        3.22.20a

#
# Table structure for table 'pagestats'
#
CREATE TABLE pagestats (
  uri varchar(96) DEFAULT '' NOT NULL,
  hits bigint(21) DEFAULT '0' NOT NULL,
  since datetime,
  huid mediumint(6) unsigned zerofill DEFAULT '000000' NOT NULL,
  dhits int(11) DEFAULT '0' NOT NULL,
  whits int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (uri),
  KEY huid (huid),
  KEY hits (hits),
  KEY dhits (dhits),
  KEY whits (whits)
);





# Server version        3.22.20a

#
# Table structure for table 'htemplate'
#
CREATE TABLE htemplate (
  luid mediumint(6) unsigned zerofill DEFAULT '000000' NOT NULL
auto_increment,
  create_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  modified timestamp(14),
  artist char(60) DEFAULT '' NOT NULL,
  featuring char(60) DEFAULT '' NOT NULL,
  song char(60) DEFAULT '' NOT NULL,
  album char(60) DEFAULT '' NOT NULL,
  author char(60) DEFAULT '' NOT NULL,
  url char(128) DEFAULT '' NOT NULL,
  accepted enum('Y','N','Pending') DEFAULT 'Y' NOT NULL,
  rank tinyint(3) unsigned zerofill DEFAULT '000' NOT NULL,
  reveal enum('show','hide','audio_only') DEFAULT 'show' NOT NULL,
  ASIN char(60) DEFAULT '' NOT NULL,
  ra_url char(128) DEFAULT '' NOT NULL,
  iuid int(10) unsigned DEFAULT '0' NOT NULL,
  alabel char(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (luid),
  KEY create_date (create_date),
  KEY modified (modified),
  KEY featuring (featuring),
  KEY artist (artist),
  KEY author (author),
  KEY song (song),
  KEY album (album),
  KEY url (url),
  KEY ASIN (ASIN),
  KEY accepted (accepted),
  KEY rank (rank),
  KEY reveal (reveal),
  KEY ra_url (ra_url),
  KEY iuid (iuid)
);




-- 
Vincent Stoessel-xaymaca@stripped
Xaymaca Studios http://www.xaymaca.com
Thread
Trying to optimize a joinKing Xaymaca3 Apr
  • Re: Trying to optimize a joinKing Xaymaca3 Apr
    • Re: Trying to optimize a joinMichael Widenius4 Apr