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

$sql="SELECT id_news,title,id_section_news FROM news WHERE id_news in (SELECT MAX(id_news) FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub ) GROUP BY id_section_news )limitLIMIT 7";

Now i want help to can fix my problem .

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.

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

$sql="SELECT id_news,title,id_section_news FROM news WHERE id_news in (SELECT MAX(id_news) FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub ) GROUP BY id_section_news LIMIT 7)";

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.

Is nested SELECT absolutely necessary? Probably this is what slows your query down. And as as vibrokatana suggests, check your indexes.

$sql="SELECT id_news,title,id_section_news FROM news WHERE id_news in (SELECT MAX(id_news) FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub ) GROUP BY id_section_news LIMIT 7)";

this code is wronge i think coz the query must be

$sql=("SELECT id_news,title,id_section_news FROM news WHERE id_news in (SELECT MAX(id_news) FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub ) GROUP BY id_section_news) LIMIT 7)";

my problem now in (GROUP BY id_section_news)

i want change it

Group by make my index so slow :(

when try remove this code
in (SELECT MAX(id_news) FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub )

and put

id_news<>$id_news_main or id_news<>$id_news_sub GROUP BY id_section_news ORDER BY id_news limit 7

all news in my index since start the site

old news :(
i want get last news from sections :(

any help to fix my problem?

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.

Yes
but nothing changed.

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).

<<removed>>

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.

Sounds like you might be better off with a join, instead of nesting selects.

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?
SELECT MAX(id_news) FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub ) GROUP BY id_section_news
(you could use the current values of $id_news_main and $id_news_sub). Also try:
EXPLAIN SELECT MAX(id_news) FROM news WHERE id_news NOT IN ($id_news_main,$id_news_sub ) GROUP BY id_section_news

nothing changed

Slow Page + High Load

Run the EXPLAIN query from shell or phpmyadmin - it will tell you which keys are being used (or not used)

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";

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

Run the EXPLAIN query from shell or phpmyadmin - it will tell you which keys are being used (or not used)

MySQL said:
#1054 - Unknown column '$id_news_main' in 'where clause'

i use template power

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";

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?
There is a problem with mysql and unoptimized nested queries. The outer query gets called as many times as inner query record count AFAIR.

old news :(

what can i do now?

forget this step? or try re- code my script without template power?

MySQL said:
#1054 - Unknown column '$id_news_main' in 'where clause'

i use template power
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.