All this depends very much on how the environment, the database modeling and the query performed and the form are configured.

Environment

If the database and its language run on the same machine, a serious resource contention condition may occur.

MySQL needs to read a lot of data from the disk and transfer it to memory, and at the same time the Ruby process communicates with the MySQL process.

All this on the same CPU and on the same bus can cause a great deal of performance degradation.

If this is the case, try separating the database server and application server into two separate hard- ware connected by a high-speed network.

Database Modeling

It's hard to talk about performance and optimization issues without talking about the model.

Inappropriate use of types (use VARCHAR as primary keys or to store numbers and dates), lack of adequate indexes, excessive normalization (needing to join many tables to retrieve information) are factors that contribute to poor performance

Query )

With proper hardware and modeling, you then have to check the query execution plan.

The most common cause of query slowness is the table scan , that is, when a query needs to read all the records in a table to return the results.

This occurs whenever you use a WHERE condition that is not linked to an index. It can also occur in other situations, such as subqueries , joins or even during sorting.

Using indexes

For example, if you have a query like this:

select id, nome, idade, sexo, endereco
from cliente
where idade = 30
order by nome

The ideal here is to create two indexes: one per idade and another one with nome with ascending sort.

This would make MySQL not need to read the table to filter the results. Looking at the index by% with% would determine which records to fetch from the truth table, and looking at the index by idade , it would not have to compare all the names to determine the order, since the index is ordered. / p>

See another example:

select id, nome, idade, sexo, endereco
from cliente
where idade = 30 and sexo = 'M'
order by nome

In the above case, MySQL could filter by nome and then look at the registry to filter idade . This is better than nothing, but could be even more efficient with an index including sexo and idade .

Finally, the indexes allow the database to know in advance which data should be retrieved to the user, without having to scan the entire table by loading the values and making comparisons.

One thing to note is that even with good indexes, using type conversion operations or transformation functions in comparison clauses can prevent them from being used. There are many possibilities, so it's always good to test the query with a tool that shows the execution plan.

What about Ruby?

If the database server is stuck, then the fault should not be from Ruby.

As far as I understand, the server is unavailable, so the processing bottleneck should be in it.

If the query was running quickly and then Ruby was reading the information retrieved, it would probably be Ruby itself.

However, there is still something to consider ...

Level of insulation

Relational databases work with certain levels of isolation, which control how different sessions and transactions can see each other's data.

Failure to see the data while the query is executed may be caused by system overhead or simply because the isolation level locks the table when it is being read.

Something that can optimize competing reading is to allow "dirty reading". To do this in MySQL it is necessary to execute the command below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

When you run this, you should be able to query the table while it is being read or changed.

For more details on the command, see documentation .