Hi,
I have a problem with a query, because it works very slow and I am trying to
analyze it with "explain".
I have read that if I use STRAIGHT_JOIN in a select query, the server will
take the tables in the order I specified them in that query.
I have tried to "explain" a query that uses STRAIGHT_JOIN , but I have seen
that the tables that appear as a result, don't appear in that order.
Here is the query:
explain select STRAIGHT_JOIN sql_calc_found_rows a.pre_title, a.title,
a.post_title, substring(a.body, 1, n.preview_size) as
preview, a.body_hash, a.date, a.time, length(a.body) as size,
a.id_categories, n.name as newspaper, sc.category,
count(act.id) as visitors, count(aco.hash) as comments from articles a
inner join newspapers n on(a.id_newspapers=n.id)
inner join sections s on(a.id_sections=s.id and n.id=s.id_newspapers)
inner join sections_categories sc on(a.id_categories=sc.id)
left join articles_count act on(a.body_hash=act.hash_articles)
left join articles_comments aco on(a.body_hash=aco.hash_articles)
where n.active_view='yes' and s.active_view='yes'
and a.date between '2005-01-01' and '2005-12-31'
group by a.body_hash order by visitors desc, comments desc, a.title limit
0,30\G
The result is below, but I have no idea if there is something wrong with my
query or what could be the problem.
Thank you for your advice.
Teddy
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: aco
type: system
possible_keys: hash_articles
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: index
possible_keys: PRIMARY,id_newspapers,date,id_categories,id_sections
key: body_hash
key_len: 32
ref: NULL
rows: 89285
Extra: Using where; Using temporary; Using filesort
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: n
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: presa.a.id_newspapers
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: s
type: eq_ref
possible_keys: PRIMARY,id_newspapers,active_view
key: PRIMARY
key_len: 8
ref: presa.n.id,presa.a.id_sections
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: sc
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: presa.a.id_categories
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: act
type: ref
possible_keys: hash_articles
key: hash_articles
key_len: 32
ref: presa.a.body_hash
rows: 1
Extra:
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
