codeigniter , mysql query performance problems

To reduce the size of the inner select's result , move the selection and join of fields name and d_order to the outer select

SELECT id,title,preview,created,image,dummy,name,d_order FROM     ( select news.id AS id,news.title AS title,preview,created,news.image,category_id ,         @num := if(@cat = category_id, @num + 1, 1) as row_number,         @cat := category_id as dummy from news use index (idx_cat)  where     news.category_id in (9,8,3,35,57,56,15,58,41,42,43,44,34,52,37,38,36,11) and    news.status = 1  having row_number <= 4  order by dummy ASC,news.created desc ) as a inner join category b on category_id=b.idorder by d_order ASC,created DESC

Problably the number of rows is still big, but we have reduced the memory. The way you are selecting requires to process the whole news table and then remove the unwanted rows, in a very expensive calculation. May be it could be more efficient if you make that preselection of news just with news.id, cat.id and the minimun fields, so the news body an heavy fields are outside the heavy select.

SELECT id,c.title,c.preview,created,c.image,dummy,name,d_order FROM     ( select news.id AS id,category_id ,         @num := if(@cat = category_id, @num + 1, 1) as row_number,         @cat := category_id as dummy from news use index (idx_cat)  where     news.category_id in (9,8,3,35,57,56,15,58,41,42,43,44,34,52,37,38,36,11) and    news.status = 1  having row_number <= 4  order by dummy ASC,news.created desc ) as a     inner join category b on category_id=b.id    inner join news c on a.id = c.id    order by d_order ASC,created DESC

Probably I have some sintax error but it's a little bit difficult to write here and I don't have the data to test. Hope you get the point where i see the problem.