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
Loading problem
Moderator: General Moderators
Re: Loading problem
SELECT pages.* is almost surely wrong. EXPLAIN the query to see where the bottleneck is.
Re: Loading problem
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
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
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
The EXPLAIN you posted above is truncated. The first line looks problematic, though.