mysql - select last record in each group for large database -


i want fetch last record in each group. have used following query small database , works -

select * logs  id in (      select max(id) logs       id_search_option = 31       group items_id     )  order id desc 

but when comes actual database having millions of rows (80,00000+ rows), system gets hanged.

i tried query, gives result in 6.6sec on average --

select p1.id, p1.itemtype, p1.items_id, p1.date_mod logs p1 inner join (      select max(id) max_id, itemtype, items_id, date_mod       logs       id_search_option = 31       group items_id) p2 on (p1.id = p2.max_id)  order p1.items_id desc; 

please !

edit:: explain 2nd query

id  select_type     table     type    possible_keys     key     key_len     ref       rows    1   primary     <derived2>         null             null     null       null      1177    using temporary; using filesort 1   primary     p1            eq_ref   primary          primary   4        p2.max_id    1    2   derived     logs               null             null     null      null      7930527   using where; using temporary; using filesort 

select *from tablename orderby unique_column desc limit 0,1; try work here 0->oth record,1->one record


Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -