help with ransack / mysql performance

[tom]

hi,

i am using ransack and i am experiencing slow queries, eg on the search sites i have a query like that:

SELECT DISTINCT COUNT(DISTINCT `tablename`.`id`) FROM `tablename` WHERE (`tablename`.`city` LIKE '%Tampa%');

~5sec

w/o distinct 

~ 6sec

w/o distinct & w/o where

~4 sec

what do you guys do? i reviewed all indexes, datatypes, memory is ok. no overload.

and yet i am feeling that i am missing something....

thx

[Ben Edwards]

your like '%Tampa% will not use an index.  Looks like you are implementing some type of search.  For this type of free text search SQL is not a good option.  Elasticsearch is worth looking at or goodle for 'rails free text serach tools'.

Ben

[D F]

That query looks suspiciously like you might be using kaminari for pagination? If so that is what is causing this slow query. See https://github.com/kaminari/kaminari#paginating-without-issuing-select-count-query for how to solve that. I don't believe ransack ever needs to count anything.