[RESOLVED] ways to reduce memory load?

Zpixel

feed_news is a large data table.

1) $result=mysql_query("SELECT * FROM feed_news WHERE(suitable clause) LIMIT 1600");
does the query above load the whole result into memory? or memory load occures after fetch proccess?

........
....
..

mysql_free_result($result);

bradgrafelman

Zpixel wrote:
does the query above load the whole result into memory?

Depends on whose memory you're talking about. It will load the results into memory as a sort of temporary table on the database server, yes. As far as the webserver and/or the PHP process, no, that statement only increases the memory usage slightly - just enough to add a reference to the new resource.

The data will be copied into PHP's memory as you use the fetch commands, so if you overwrite the same variable on each iteration of some sort of loop, then you should only be using enough memory to hold one row of data at any given time.

Zpixel

I don't know why, but the query bellow makes my hosting account to stop working:

mysql_query("SELECT news_id FROM feed_statics $condition ORDER BY news_clicks DESC LIMIT $total_news")

$condition=where(news_id IN (SELECT id FROM feed_news WHERE update_time>$time) and click_time>$time) and and news_clicks>0

update_time>$time return a maximum of 5000 records
click_time>$time return a maximum of 1000 records

Zpixel

I don't know why, but the query bellow makes my hosting account to stop working:

mysql_query("SELECT news_id FROM feed_statics $condition ORDER BY news_clicks DESC LIMIT $total_news")

$condition=where(news_id IN (SELECT id FROM feed_news WHERE update_time>$time) and click_time>$time) and and news_clicks>0

update_time>$time return a maximum of 5000 records
click_time>$time return a maximum of 1000 records

dagon

Zpixel;10925860 wrote:
I don't know why, but the query bellow makes my hosting account to stop working:

tried talking to your host?

NogDog

It would probably be more efficient to do some sort of join, instead of populating a huge IN() that probably then requires a complete table scan of the first table and then a comparison of each and every id against that list of id's. Something like...

SELECT DISTINCT fs.news_id FROM feed_statics AS fs
INNER JOIN feed_news AS fn ON fs.news_id = fn.id
WHERE fn.update_time > $time AND fs.click_time > $time AND fs.news_clicks > 0
ORDER BY fs.news_clicks

You'll probably want indexes set on each of those fields in the ON and WHERE clauses, if they're not already indexed.

Zpixel

thanks. it was indexing problem.