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