It’s been a few weeks now that the loading / display of cards takes 4 seconds once we click an auto-suggested text in the search bar. Could this joined query be the reason?
Could, yes. But the problem may not be in the query itself, but rather the lack of indexes. Indexes are used by the database software to speed up queries by knowing where to look up, instead of sequentially scanning all rows (which is a slow operation if you have many rows).
Another possibility that needs to be checked, is that you might be calling this server connect multiple times at the same time. This needs to be checked using your browser’s developer tools in the network tab, ensure after you click the auto-completion this server connect is only called once.
Try doing a simple select where keyword.keyword is equal to the string you want to search, see how long it takes. And how many keywords do you have on your DB?
Most likely you’ll need to create an index for the column keyword. I’m out for the day, hope someone chimes in to help you further or you figure how to create an index on a column. If you’re using Wappler migrations (NodeJS I think) search for Knex index
Not entirely sure i understand your workflow, but i notice that here:
for your second query, you are using {{qryKeywordFilter[0].keyword}} which means that it will filter the second query with only the first result from the first query. I don’t think that is what you want - to use only the first keyword found?
also, if you are not outputting the first query result on the page, then make sure the output checkbox for it in server connect is not turned on.
I tested that and it’s still slow.
I only have like 3 dozens of keywords for now, some are repeating - Consultants A and B could have the same “SEO” keyword - hence the DISTINCT.
LOL… this is pure Chinese, but thanks anyway
I feel I’m still too small for indexing to be critical. My keyword table looks like this (Consultants can enter up to 12 keywords):
Thanks, fixing that. So output is just for display on page purpose.
As for the query, yes indeed, I want only one keyword (filtered by the 1st query) so I can match it with the same keyword under each Consultant if they did input that keyword. That’s my keyword table (userID = consultants):
Yes, that one keyword will then be matched with those Consultants who happened to have saved the same keyword.
You can test the search: https://livehacks.co/#searchBar and type in marketing and selected whatever suggestion. How slow is it?
Think of it as the data which transfers from server to client. So the output option in Server Connect means whatever result it produces will be output in JSON and picked up by the page for use with App Connect. If you want to use data on the server but never use it on the front end, don’t tick the output box.