Hi,
I have finally modified that long query and splitted into smaller ones.
Now the main query is:
select sql_calc_found_rows
a.pre_title, a.title, a.post_title, substring(a.body, 1, 250) as preview,
a.hash, a.date, a.time, length(a.body) as size, a.id_categories,
n.name as newspaper, sc.category
from articles a, newspapers n, sections_categories sc
where a.id_newspapers=n.id
and a.id_categories=sc.id
and a.active_view=1
and a.id_categories=20
limit 0,30;
For each of those 30 records which are returned, I make other 3 queries:
select count(*) from articles_comments where hash_articles='[an MD5 hash
with 16 characters]';
select count(*) from articles_count where hash_articles='[an MD5 hash with
16 characters]';
select name, email, query from articles_authors where hash_articles='[an MD5
hash with 16 characters]';
Well, now instead of making a big query, MySQL will make 91 queries.
I have tested the program, but it doesn't work faster at all.
Do you have any idea what could be wrong?
There are almost 100.000 records in the database, and this query should
return 10121 records.
Here is the data structure. Please tell me if you think there is something
bad in it.
Thank you.
Teddy
DROP TABLE IF EXISTS `articles`;
CREATE TABLE `articles` (
`id_newspapers` smallint(3) unsigned NOT NULL default '0',
`id_sections` smallint(3) unsigned NOT NULL default '0',
`id` int(6) unsigned NOT NULL auto_increment,
`hash` char(16) NOT NULL default '',
`url` varchar(255) NOT NULL default '',
`full_url` varchar(255) NOT NULL default '',
`pre_title` varchar(255) not null default '',
`title` varchar(255) NOT NULL default '',
`post_title` varchar(255) not null default '',
`body` text NOT NULL,
`body_hash` char(32) NOT NULL default '',
`article_ident` varchar(255) not null default '',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`id_categories` tinyint(3) unsigned not null default '0',
`active_view` tinyint(1) unsigned not null default '1',
PRIMARY KEY (`id_newspapers`,`id_sections`,`id`),
UNIQUE KEY `id_newspapers` (`id_newspapers`,`body_hash`),
UNIQUE KEY `hash` (`hash`),
KEY `date` (`date`),
KEY `id_categories` (`id_categories`),
FULLTEXT KEY `full` (`pre_title`,`title`,`post_title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_authors`;
CREATE TABLE `articles_authors` (
`hash_articles` char(16) NOT NULL default '',
`name` varchar(30) NOT NULL default '',
`email` varchar(255) not null default '',
`query` varchar(255) not null default '',
UNIQUE KEY `hash` (`hash_articles`,`name`),
KEY `hash_articles` (`hash_articles`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_comments`;
CREATE TABLE `articles_comments` (
`hash` char(16) NOT NULL default '',
`hash_articles` char(16) NOT NULL default '',
`hash_users` char(16) NOT NULL default '',
`body` text NOT NULL,
`hash_original` char(16) not null default '',
`comment_type` enum('public','private') NOT NULL default 'public',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`remote_address` varchar(255) not null default '',
PRIMARY KEY (`hash`),
KEY `hash_articles` (`hash_articles`),
KEY `hash_users` (`hash_users`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_count`;
CREATE TABLE `articles_count` (
`id` int(10) unsigned NOT NULL auto_increment,
`hash_articles` char(16) NOT NULL default '',
`hash_users` char(16) not null default '',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`remote_address` varchar(255) NOT NULL default '',
`user_agent` varchar(255) not null default '',
PRIMARY KEY (`id`),
UNIQUE KEY `hash` (`hash_articles`,`date`,`remote_address`),
KEY `hash_articles` (`hash_articles`),
KEY `hash_users` (`hash_users`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `newspapers`;
CREATE TABLE `newspapers` (
`id` smallint(3) unsigned NOT NULL auto_increment,
`label` varchar(20) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`script` varchar(20) NOT NULL default '',
`first_page` varchar(255) NOT NULL default '',
`base_url` varchar(255) not null default '',
`email` varchar(255) not null default '',
`importance` tinyint(3) unsigned not null default '0',
`category` tinyint(1) unsigned not null default '3',
`frequency` enum('week','month') NOT NULL default 'week',
`sect_skip` smallint(5) unsigned default NULL,
`sect_end` smallint(5) unsigned default NULL,
`art_skip` smallint(5) unsigned default NULL,
`art_end` smallint(5) unsigned default NULL,
`preview_size` smallint(3) unsigned not null default '250',
`view_size` smallint(5) unsigned default NULL,
`active_download` enum('yes','no') not null default 'yes',
`active_view` enum('yes','no') not null default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `label` (`label`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `sections`;
CREATE TABLE `sections` (
`id_newspapers` smallint(3) unsigned NOT NULL default '0',
`id` smallint(3) unsigned NOT NULL auto_increment,
`label` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`url` varchar(255) not null default '',
`sect_ident` varchar(255) not null default '',
`sect_type` varchar(255) not null default '',
`category` tinyint(3) unsigned not null default '0',
`lang` tinyint(3) unsigned not null default '1',
`sect_skip` smallint(5) unsigned default NULL,
`sect_end` smallint(5) unsigned default NULL,
`art_skip` smallint(5) unsigned default NULL,
`art_end` smallint(5) unsigned default NULL,
`active_download` enum('yes','no') not null default 'yes',
`active_view` enum('yes','no') not null default 'yes',
PRIMARY KEY (`id_newspapers`,`id`),
UNIQUE KEY `id_newspapers` (`id_newspapers`,`label`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `sections_categories`;
CREATE TABLE `sections_categories` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`ordine` tinyint(3) unsigned NOT NULL default '0',
`category` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
From: <[EMAIL PROTECTED]>
> > From: <[EMAIL PROTECTED]>
> > Subject: Re: more queries vs a bigger one
> >
> >
> > > Hello,
> > > approx. how long does it take your "big" query to run as it is now?
> Are
> > > these queries appending a table? or are they buiding a result (from a
> > > "chain" of queries)? Have you tried separating them out? Any
> difference?
> > > -sam
> > >
> > >
> >
> > That query takes more than 2 minutes.
> > I have a table with less than 90.000 records, and this is the bigger
> table
> > from the database, so the query is very slow, because as you may see,
> the
> > tables are not very big.
> >
> > But that query might return more than 9.000 records, even though I limit
> it
> > to first 30.
> >
> > I will split it into more smaller queries and I will report if it works
> > faster.
> >
> > The query is something like the example below, but I don't have it here
> > right now to send it to the list, but I will send it soon:
> >
> > select a.pre_title, a.title, a.post_title, substring(a.body, 1,
> > n.preview_size) as preview, n.title as publication, a.id_category,
> cs.label,
> > count(aco.hash_articles) as comments, count(act.hash_articles) as
> counter
> > from articles a
> > left join newspapers n on(a.id_newspapers=n.id)
> > left join sections s on(a.id_sections=s.id and s.id_newspapers=n.id)
> > left join sections_categories cs on(a.id_categories=cs.id)
> > inner join articles_counters act on(a.body_hash=act.hash_articles)
> > inner join articles_comments aco on(a.body_hash=aco.hash_articles)
> > where a.id_category=20
> > and a.date between '2005-01-01' and '2005-12-31'
> > group by a.body_hash order by rand();
> >
> > The table newspapers has only 20 records. The table sections has under
> 300
> > records, but the tables articles_counter and articles_comments might
> have
> > many records... hundread of thousands.... millions.
> >
> > Thanks.
> >
> > Teddy
> >
>
> This is your original query (above) unwrapped and tabified:
> select a.pre_title
> , a.title
> , a.post_title
> , substring(a.body, 1, n.preview_size) as preview
> , n.title as publication
> , a.id_category
> , cs.label
> , count(aco.hash_articles) as comments
> , count(act.hash_articles) as counter
> from articles a
> left join newspapers n
> on(a.id_newspapers=n.id)
> left join sections s
> on(a.id_sections=s.id
> and s.id_newspapers=n.id)
> left join sections_categories cs
> on(a.id_categories=cs.id)
> inner join articles_counters act
> on(a.body_hash=act.hash_articles)
> inner join articles_comments aco
> on(a.body_hash=aco.hash_articles)
> where a.id_category=20
> and a.date between '2005-01-01' and '2005-12-31'
> group by a.body_hash
> order by rand();
>
> I see ONE GLARING problem with this query right away:If you weren't using
> MySQL, this would be an illegal query.
>
> Your GROUP BY clause does not contain enough columns and the column it
> *does* contain doesn't appear in your SELECT clause. MySQL has a way of
> making queries like this "work" but your results are not guaranteed to be
> deterministic.
>
> Another issue that is killing your speed is your ORDER BY RAND();
>
> I believe it would be much faster to break this into 3 smaller queries:
> first to pick your articles, second to get your statistics, the third will
> combine the first two with some additional information to provide your
> finished report.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
