Optimization needed for /api/srch/profiles API query

[jywarren]

Probably due to very high number of users in the system (300k+?) this query needs some optimization as I believe it's responsible for some outages, and is running very slow!

https://publiclab.org/api/srch/profiles?srchString=aya

@icarito - and be aware of this @milaaraujo @stefannibrasil 👍

[jywarren]

I believe this is a double-ended string match that's running against the whole table - inefficient...

plots2/app/services/srch_scope.rb

Lines 5 to 8 in de4d09f

	User.order('id DESC')
	.where(status: 1)
	.where('username LIKE ?', '%' + input + '%')
	.limit(limit)

This is especially apparent when trying to autocomplete usernames after typing @... in a comment...

plots2/app/models/user.rb

Lines 44 to 46 in de4d09f

	def self.search(query)
	User.where('MATCH(username, bio) AGAINST(?)', query)
	end

Can we use this new full-text search function instead of the above code?

[jywarren]

#3148 - one attempt at this... any suggestions?

[jywarren]

Optimizing front end w @ryzokuken too:

https://github.com/ichord/At.js

plots2/app/views/comments/_form.html.erb

Lines 76 to 88 in de4d09f

	var at_config = {
	at: "@",
	callbacks: {
	remoteFilter: function(query, callback) {
	$.getJSON("/api/srch/profiles?srchString=" + query, {}, function(data) {
	if (data.hasOwnProperty('items') && data.items.length > 0) {
	callback(data.items.map(function(i) { return i.docTitle }));
	}
	});
	}
	},
	limit: 20
	},

[jywarren]

caching example here: https://github.com/ichord/At.js/wiki/How-to-use-remoteFilter#caching-example

[jywarren]

i also limited to 10 results in the reply, in
d4f1624

[jywarren]

Opened a caching issue at #3149!

[jywarren]

@ryzokuken is working on a debouncing strategy https://css-tricks.com/debouncing-throttling-explained-examples/

[jywarren]

We could further optimize the User query above by selecting only username, status, id... but the full text search also searches bio so we'd need to make an alternate form of User.search that does not search bios... i'll open another PR for that.

[jywarren]

#3150 - searching only usernames!

[jywarren]

ok awesome, previously this had ~600 results, now only 10: https://publiclab.org/api/srch/profiles?srchString=aya

[stefannibrasil]

yeah, we were planning to set aside some weeks to work only on improving the search service, but we can start this week, beginning with the #3069.

[stefannibrasil]

We believe we addressing those concerns here #3134 could you please take a look there? we left some comments along the code reflecting the questions we had while working on the endpoint. Thanks!

[jywarren]

Also implementing @ryzokuken's optimization here: https://www.irccloud.com/pastebin/yTikGnHV/

[jywarren]

Ok, and working on this in #3172 but unable to get the debounce to work yet...

[Radhikadua123]

@jywarren As I can see, #3134 was merged. Is this issue still valid ? If yes, then I can have a deep look inside it.

[jywarren]

#3172 and #3149 are still unresolved, and we'd love help with them!! Thank you!

[Radhikadua123]

I see. I would love to give it a try later on.

[jywarren]

awesome, thanks!

…

On Tue, Oct 30, 2018 at 6:15 PM Radhika Dua ***@***.***> wrote: I see. I would love to give it a try later on. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#3147 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABfJ1k9mg_NN_-OswDS624ykPSuG0dEks5uqM9zgaJpZM4VgISi> .

[jywarren]

@milaaraujo is this potentially of interest for the API work you're doing?

[milaaraujo]

Not directly... I think! What exactly needs to be done here? We are already using the full-text search function. @jywarren

[jywarren]

I'm thinking this on caching results: #3149 potentially

[jywarren]

Thanks!

[milaaraujo]

Can we close this one, @jywarren?

[jywarren]

Yes, esp since it's being worked on in #4670. Thanks!