Slow EC2 MySQL database (xampp) access

[DiogoIDENG]

Hello,
I'm using AWS EC2 as cloud server for MySQL database (xampp) and I'm getting data so slow that my app crash. Is it network configuration, weak instance type?

[SGaist]

Hi,

How exactly does it crash ?
What's the database size ?
What kind of query are you sending ?
What is your connection speed ?

[DiogoIDENG]

The problem starts after using a lineedit, where for each letter, she gets data from the database.
If I wait for the getting data process, it works, but it is so slow.
The database is 976.0 KB.
An example of query type is:
query_pesquisa.prepare("SELECT * FROM lista_artigos Where Designacao LIKE :designacao AND ID=:id_art");
The latency is slow, and I try to use a better instance type, but it is still slow.

One thing that I saw is, if I only have one query in the function, it is fast, but if I use a query inside another query, it is slow.

I don't understand.

[DiogoIDENG]

latency is low(44ms)*

[JonB]

@DiogoIDENG
There is nothing wrong with your query per se. A 1MB (is it really that small or did you mean 1GB?) is tiny. A nested query will run slower, but that is done up at the database server side, no WAN/latency etc. I'm surprised you even notice a speed difference, not sure what to make of that.

On a WAN/where you have latency issuing a query for every letter typed is not a good idea. You could compensate for that by doing further work client-side instead. Think about your query:

Where Designacao LIKE :designacao AND ID=:id_art

I assume LIKE :designacao is used to pattern match against the characters being typed into the line edit. Let's say user starts by typing A as first character. The query returns all rows where Designacao starts with or has an A. Now the user types B as second character. You will issue a new query asking for those starting with or having AB. But by definition that will we a subset of those with A which you already have fetched to the client. So instead of the requerying the database you could just pick from those you already have without going back to the database. (Either do it yourself or use a QSortFilterProxyModel.) Miles faster. You have to deal with if user deletes the A or other such change which makes the original matching rows invalid, but that's not hard to do.

Furthermore AND ID=:id_art looks like ID is the primary (or other unique?) key. In that case the query can only return either one or no records. Even less reason to go re-querying the database.