Page 1 of 1

Loading problem

Posted: Tue Feb 28, 2012 4:04 am
by fabby
Hellp ppl!
I have a problem with a website if you can help me.
For example, this query, the hosting told me that it loads slowly:

Executed 1 min ago for 3 sec on Database:
select pages.*, subcat.name as subcat_name, subcat.seo_link as subcat_seo_link,cat.name as cat_name, cat.seo_link as cat_seo_link from pages left join categories as subcat on subcat.id=pages.id_parent left join categories as cat on cat.id=pages.CatID where pages.CatID='21' and pages.id<'34883' and pages.visible='yes' order by id desc limit 5

Table pages has over 30.000 rows, and 260mb.
Table categories has max 100rows, is very small.
I have indexes on all fields that are in where condition.

So...where can be the problem?
And where i take this sql and insert in phpmyadmin and execute, the time is 0.3535 sec on executing the sql..
So, where do you believe that is the problem?
Thanks
Wait your response

Re: Loading problem

Posted: Tue Feb 28, 2012 4:59 am
by Celauran
SELECT pages.* is almost surely wrong. EXPLAIN the query to see where the bottleneck is.

Re: Loading problem

Posted: Tue Feb 28, 2012 5:43 am
by fabby
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pages index_merge PRIMARY,visible,CatID,id CatID,visible 4,1 NULL 2167 Using intersect(CatID,visible); Using where; Using...
1 SIMPLE subcat eq_ref PRIMARY PRIMARY 4 zuarticl_zuart.pages.id_parent 1
1 SIMPLE cat eq_ref PRIMARY PRIMARY 4 zuarticl_zuart.pages.CatID 1

this i recieve

Re: Loading problem

Posted: Wed Feb 29, 2012 6:22 am
by fabby
for example tthose from the hosting told me even this sql: update pages set visits='122' where id='14200' is loading hard...what do you think?

Re: Loading problem

Posted: Wed Feb 29, 2012 6:42 am
by Celauran
The EXPLAIN you posted above is truncated. The first line looks problematic, though.