(Query) Slow Page

Portal Home > Knowledgebase > Articles Database > (Query) Slow Page

Posted by 3okl, 08-29-2008, 01:50 PM
Hello I have problem in my script My problem start when make query to get last 5 news from news section (Group by) My Query Now i want help to can fix my problem .
Posted by healthnut101, 08-29-2008, 06:13 PM
I'm not really sure what the question is but I think you may be having a problem because you have put two limit words in your statement.
Posted by 3okl, 08-29-2008, 06:19 PM
i try remove limit but nothing changed i want change groupby to same code to make the site more fast. My probelm again i want get last 5 post from news sections Sample : Wrold : Newsid Sport : Newsid Pc : Newsid Internet : Newsid this what i mean thanks
Posted by vibrokatana, 08-29-2008, 09:26 PM
That will keep it from generating a full listing of the newest IDs in the sections, which may be the issue. id_news and id_section_news should probably be indexed.
Posted by livechatr, 08-30-2008, 01:05 AM
Is nested SELECT absolutely necessary? Probably this is what slows your query down. And as as vibrokatana suggests, check your indexes.
Posted by 3okl, 08-30-2008, 06:13 AM
this code is wronge i think coz the query must be my problem now in (GROUP BY id_section_news) i want change it Group by make my index so slow
Posted by 3okl, 08-30-2008, 08:42 AM
when try remove this code and put all news in my index since start the site old news i want get last news from sections
Posted by 3okl, 08-30-2008, 09:42 PM
any help to fix my problem?
Posted by foobic, 08-31-2008, 02:02 AM
You need the MAX ... GROUP BY to get you the latest article from each section. Do you have an index on id_section_news? Run "SHOW CREATE TABLE news" if you're not sure.
Posted by 3okl, 08-31-2008, 09:21 AM
Yes but nothing changed.
Posted by SolidTools, 08-31-2008, 09:55 PM
Have you properly index the two columns? $id_news_main,$id_news_sub Using NOT is not good practice as well, because the database has to look all the records (A full cricle). <> Last edited by sirius; 08-31-2008 at 10:48 PM.
Posted by 3okl, 09-02-2008, 04:49 PM
my problem not in $id_news_main or $id_news_sub. my problem in Group BY only . because when try change it to {order by} everything is oki but order by show me last news from 1 section only but i need last news from sections.
Posted by Sheps, 09-02-2008, 06:00 PM
Sounds like you might be better off with a join, instead of nesting selects.
Posted by foobic, 09-02-2008, 06:59 PM
The outer query probably isn't a problem though - it's just pulling a few records based on a list of values for id_news, which should be the primary key. But just to check, if you run the inner query alone, is it still slow? (you could use the current values of $id_news_main and $id_news_sub). Also try:
Posted by 3okl, 09-04-2008, 02:27 PM
nothing changed Slow Page + High Load
Posted by foobic, 09-04-2008, 06:20 PM
Run the EXPLAIN query from shell or phpmyadmin - it will tell you which keys are being used (or not used)
Posted by slawek22, 09-05-2008, 11:15 AM
Use INNER JOIN Instead of subqueries. Subqueries are doing very bad in mysql (or maybe without subqueries / joins): $sql="SELECT id_news,title,id_section_news FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub) GROUP BY id_section_news ORDER BY id_section_news DESC LIMIT 7"; Last edited by slawek22; 09-05-2008 at 11:19 AM.
Posted by 3okl, 09-05-2008, 11:40 AM
ok this a good query but not Max news mynews = 2000 news from 6 sections World Tech Arabic accedent Simple Art i want get last 6 news from 6 sections thanks
Posted by 3okl, 09-05-2008, 11:44 AM
MySQL said: #1054 - Unknown column '$id_news_main' in 'where clause' i use template power Last edited by 3okl; 09-05-2008 at 11:50 AM.
Posted by slawek22, 09-05-2008, 12:45 PM
Maybe try: $sql="SELECT id_news,title,id_section_news FROM news GROUP BY id_section_news ORDER BY id_section_news DESC LIMIT 7"; There is a problem with mysql and unoptimized nested queries. The outer query gets called as many times as inner query record count AFAIR.
Posted by 3okl, 09-05-2008, 02:57 PM
old news what can i do now? forget this step? or try re- code my script without template power?
Posted by foobic, 09-05-2008, 07:26 PM
Replace your php variables with typical values - in this case the id of your main news article - and run EXPLAIN again. If this simple query is slow then nothing else has any chance.