codeigniter , mysql query performance problems

Question

I am using codeigniter and mysql and applied in a server with 200 MB memory and 1 GB CPU, got 40.000 rows in table a (index : idx_cat) and 44 rows (index : primary) in category and i need to get top 4 of then in each category , here's my query

SELECT id,title,preview,created,image,dummy,name 
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,
        name,d_order
 from news use index (idx_cat) inner join category b on category_id=b.id
 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 
order by d_order ASC,created DESC



id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    **29639**   Using filesort
2   DERIVED     b   ALL     PRIMARY,id  NULL    NULL    NULL    **44**  Using where; Using temporary; Using filesort
2   DERIVED     news    ref     idx_cat     idx_cat     **4**   b.id    846     Using where    

and got other 6 simple join like

   select id,name from others a inner join b on a.cat_id = b.id

The site is loading quite fast approximately 1s or 2s top,but if open in another tab while the one loading it bit slow like 5-7s.

The weird thing is cpu usage is reach 100 % and memory usage got +_ 40 MB to finish one view (im sure there is no other opened) but CI PROFILING its say just user 4MB.

I've also has load model,helper,and library on demand just two (url and form) that I put in autoload file.

And if I open then 5 until 10 windows it say out of memory, have you guys any suggestion what happen with this thing , it's drive me crazy -_-

Answer 1

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.id
order 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.