der_tom
(der_tom)
October 12, 2017, 2:16pm
1
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
D_F
(D F)
January 29, 2018, 10:41pm
3
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.