30.328 1 select count(*) as C from articles_category c inner join db_news.articles a using (article_id) inner join media m using (media_id) where 1
Query Explanation table type possible_keys key key_len ref rows Extra c index PRIMARY PRIMARY 8 41551 Using index a eq_ref PRIMARY,media_id PRIMARY 4 db_news.c.article_id 1 m eq_ref PRIMARY PRIMARY 4 db_news.a.media_id 1 Using index Here are the tables. I think the problem present with just 2 tables as well, just a simple inner join on two tables with a primary key and an indexed foreign key. This seems like a pretty basic query, but I must be doing something wrong because 30 seconds to do a count can't be right. Thx, Tac --- # # Table structure for table `articles_category` # CREATE TABLE articles_category ( article_id int(11) NOT NULL default '0', category_id int(11) NOT NULL default '0', PRIMARY KEY (article_id,category_id), KEY category_id (category_id) ) TYPE=MyISAM PACK_KEYS=1; # # Table structure for table `articles` # CREATE TABLE articles ( article_id int(11) NOT NULL auto_increment, : (other stuff) site_id int(11) NOT NULL default '0', PRIMARY KEY (article_id), KEY edition (edition_id), KEY source_date (source_date), KEY site_id (site_id), KEY media_id (media_id), FULLTEXT KEY headline (headline,subhead) ) TYPE=MyISAM PACK_KEYS=1; # # Table structure for table `media` # CREATE TABLE media ( media_id int(11) NOT NULL auto_increment, : (other stuff) PRIMARY KEY (media_id) ) TYPE=MyISAM PACK_KEYS=1; ----- Original Message ----- From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, August 17, 2002 11:13 AM Subject: Re: slow query in MySQL 4 > Tac, > Saturday, August 17, 2002, 12:57:24 AM, you wrote: > > T> A simple query in MySQL 4 that gets a count from 3 inner joined tables: > > T> select count(*) as C from quotes q inner join articles a using (article_id) > T> inner join media m using (media_id) > > T> article_id and media_id are int(11), and are the primary keys for their > T> respective tables. When used as foreign keys, they are also int(11), and > T> are indexed. It's taking about 10 seconds to return the count, ~3000, from > T> tables that aren't all that big (quotes: 3000, articles: 100,000; media: > T> 1500). > > T> I plan to use the new MySQL 4 row count feature soon, but the code I'm > T> working on needs to work on both MySQL 3 and 4 for now. > > T> I believe that the query works significantly faster on MySQL 3. > > T> Any ideas? > > Could you show the output of EXPLAIN SELECT? > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
