HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Slow LIKE search on HS_Request_History

#1

Hi there!

Our users where complaining that HelpSpot was running slow. I did some investigating and it wasn’t a consistent slowness at any particular function, but most problems were around sending emails or displaying the initial admin.php screen.

I monitored the database server and found that a query was occurring quite often (every few minutes?) and was consuming 100% of a CPU on our PostgreSQL instance:

SELECT “xRequest” FROM “HS_Request_History” WHERE “tNote” ILIKE $1 AND “tNote” ILIKE $2

(This was retrieved via "select * from pg_stat_activity where usename = ‘helpspot’ " which unfortunately doesn’t show me the values of $1 and $2.).

We have 1.7 million rows in our HS_Request_History table, so I can understand it being slow, especially if this is a wildcard search.

However, I don’t know what function is causing this particular search to occur. A full-text search from the “Data Search” tab seems to use a different query (involving a score and sorting by date).

Question: Do you know which part of HelpSpot would be generating the above tNote LIKE query?

Thanks!

#2

Hi John,

Yes, this is due to someone using the full text search condition of a filter. Currently it runs the full text search aspect as it’s own query for performance reasons on some platforms.

So somebody has a filter that is using the full text condition and whenever it runs to update its count or is actually listed you’re seeing this.

You can find filter with full text conditions using this query:

SELECT * FROM “HS_Filters” WHERE “tFilterDef” ILIKE “%sSearch%”

At the very least you’ll want to make sure they all have HS_Filters.fShowCount set to 0 so that they don’t run to generate counts and only run when people actually click on them to view the results.

However, if there’s more than a few it’s usually an indication that some additional data needs to be tracked in a more formal manner (as a rep tag or custom field) so that it can be properly filtered rather than being searched for in a filter by full text scan.

Let us know if that fixes you up or if we need to investigate further.

#3

Yep, that turned out to be spot-on. There was only one global filter using a full-text search that also had “show count” turned on. So, I turned it off!

Thanks for your help yet again!

#4

Excellent! Let us know if you have any other trouble.