Improve speed of basic API search function

[jywarren]

Right now, going to https://publiclab.org/search and typing "india" and pressing submit takes a long time.

https://oss.skylight.io/app/applications/GZDPChmcfm1Q/recent/6h/endpoints/Srch::Search%20%5BGET%5D%20srch%2Fall

https://oss.skylight.io/app/applications/GZDPChmcfm1Q/1554726720/12h/endpoints/Srch::Search%20%5BGET%5D%20srch%2Fall

4 seconds to 51 seconds...

Much of this is searching rusers -- ~8 seconds:

SELECT  DISTINCT `rusers`.* FROM `rusers` INNER JOIN `node` ON `node`.`uid` = `rusers`.`id` INNER JOIN `node_revisions` ON `node_revisions`.`nid` = `node`.`nid` WHERE (MATCH(bio, username) AGAINST(? IN BOOLEAN MODE)) AND (rusers.status = ?) AND (node_revisions.status = ?) ORDER BY node_revisions.timestamp DESC LIMIT ?

Can we optimize this query?

[jywarren]

[gautamig54]

Since Public Labs has a lot of data for a search query, we can think about using a search engine like ElasticSearch or Solr with Sunspot.
I have mentioned the detailed implementation of Solr with Sunspot in my GSoC proposal:
https://publiclab.org/notes/gautami_gg/04-09-2019/gsoc-proposal-notification-system-and-ux-improvements?_=1554885329
What do you think about it @jywarren ?

[jywarren]

Hi Gautami! Thank you!!! We've actually tried to run Solr/Sunspot but epically failed, and found that it was very difficult to keep running stably. We're currently using MySQL full-text search, and I am hoping we can find a way to optimize that; are there indices we can add, or other ways to speed it up? Solr/Sunspot integration ended up being incredibly complex, and not very well supported. I really appreciate the suggestion though!!!

…

On Wed, Apr 10, 2019 at 5:34 AM Gautami Gupta ***@***.***> wrote: Since Public Labs has a lot of data for a search query, we can thing about using a search engine like ElasticSearch or Solr with Sunspot. I have mentioned the detailed implementation of Solr with Sunspot in my GSoC proposal: https://publiclab.org/notes/gautami_gg/04-09-2019/gsoc-proposal-notification-system-and-ux-improvements?_=1554885329 What do you think about it @jywarren <https://github.com/jywarren> ? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#5419 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABfJ0BZ4RG04k_jQVNywFCYH_LsnBEkks5vfa_rgaJpZM4cjXTZ> .