Re: more queries vs a bigger one
LL 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)
&
Re: more queries vs a bigger one
"Octavian Rasnita" <[EMAIL PROTECTED]> wrote on 07/19/2005 02:45:58 AM: > 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. I am assuming that `articles`.`body_hash` has an index on it and is unique. I assume it is unique and indexed because you are using it as a foreign key to the `articles_counters` and `articles_comments` tables. If you do not have an index on `articles`.`body_hash`, add one then try your query again. If it is still too slow, try this query: /* start query */ CREATE TEMPORARY TABLE tmpArtHash (key(body_hash, rndval)) SELECT body_hash, rand() as rndval FROM articles WHERE id_category=20 AND `date` between '2005-01-01' and '2005-12-31'; CREATE TEMPORARY TABLE tmpArtCounters (key(body_hash)) SELECT th.body_hash , count(aco.hash_articles) as comments , count(act.hash_articles) as counter FROM tmpArtHash th LEFT JOIN articles_counters act on th.body_hash=act.hash_articles LEFT JOIN articles_comments aco on th.body_hash=aco.hash_articles GROUP BY th.body_hash 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 , tc.comments , tc.counter FROM tmpArtHash th INNER JOIN articles a on a.body_hash = th.body_hash left join newspapers n on a.id_newspapers=n.id left join sections s o
Re: more queries vs a bigger one
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more queries vs a bigger one
Hello. It is a hard task to answer if we don't see your queries and tables' structure. Sometimes several small queries could be faster than a big one. For example, often, query with subqueries or union runs slower than few queries which use temporary tables. "Octavian Rasnita" <[EMAIL PROTECTED]> wrote: > Hi, > > I have a big query that involves searching in more tables, and I think this > might be slower than creating more smaller queries. What do you think, is > this true generally? > > The query searches in a big table but it also counts the number of records > from other 2 tables based on a criteria, and usually the result is a big > number of records, but the final result is limited using "limit 0,30". > > So I am wondering... > Could it work faster if I won't count(*) the number of records in those 2 > tables, but get the result (only 30 records), then for each separate record > use a separate query that gets that number? > > I don't know, could 31 queries work faster than a single bigger and complex > query? > > Thank you. > > Teddy > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 > Hi, > > I have a big query that involves searching in more tables, and I think > this > might be slower than creating more smaller queries. What do you think, is > this true generally? > > The query searches in a big table but it also counts the number of records > from other 2 tables based on a criteria, and usually the result is a big > number of records, but the final result is limited using "limit 0,30". > > So I am wondering... > Could it work faster if I won't count(*) the number of records in those 2 > tables, but get the result (only 30 records), then for each separate > record > use a separate query that gets that number? > > I don't know, could 31 queries work faster than a single bigger and > complex > query? > > Thank you. > > Teddy > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
more queries vs a bigger one
Hi, I have a big query that involves searching in more tables, and I think this might be slower than creating more smaller queries. What do you think, is this true generally? The query searches in a big table but it also counts the number of records from other 2 tables based on a criteria, and usually the result is a big number of records, but the final result is limited using "limit 0,30". So I am wondering... Could it work faster if I won't count(*) the number of records in those 2 tables, but get the result (only 30 records), then for each separate record use a separate query that gets that number? I don't know, could 31 queries work faster than a single bigger and complex query? Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
