help with ransack / mysql performance

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

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

That query looks suspiciously like you might be using kaminari for pagination? If so that is what is causing this slow query. See GitHub - kaminari/kaminari: ⚡ A Scope & Engine based, clean, powerful, customizable and sophisticated paginator for Ruby webapps for how to solve that. I don’t believe ransack ever needs to count anything.