Reason for search results taking 4 seconds ⏱

Hello gents,

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?

Oh and which of these two I should use for when the page should scroll to the card results?
image

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.

1 Like

Probably already know this, but just in case:

If you are using a MySQL or MariaDB database, I have found that setting the collation to utf8mb4i instead of utf8 helps speed things up.

Also, the database type should be set at InnoDB rather than MyISAM.

2 Likes

Please do tell more? I’m using MySQL. So isn’t each row indexed with the row number? If not, how do I implement this?

(My servConn is called once)

Thanks, let me check that…

Can you show your query “where” fields?

Funnily enough, I wrote a feature request a while back:

If it’s a primary key (id) it’s indexed by default, otherwise it’s not

Also another feature request:

My API action has 2 steps. One that fetches the matching keywords:

One for the results:

Where keyword.keyword

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 :sweat_smile:
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):

Easiest way, in phpMyAdmin go edit that column and see if you find a checkbox called “index” (may be a lighting bolt)

How many rows do you have at the total? Less than 100? Shouldn’t take 4 seconds

Yes.

So your first query may return 5 keywords, but you only want to use just the first one?

There? I hit Index then? I’m f*cking scared man! :laughing:


(My table has less than 100 rows, it’s for testing only now)

Yes, don’t worry about it, it’s possible to revert

Edit: wtf you already have an index on it
Edit 2: my mistake

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?

No index on keywords, just the regular primary and indexed userID.

1 Like

What does Cardinality 82 mean?

I get mixed results when clicking the buttons below the search bar. From 100ms to 2seconds for the server action to run:

Where is this site hosted? On what server?

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.

1 Like