Slow query using order by

[anuar]

Hi,

There's no option for an Entitiy to not use "order by" (Default Order in Edit Entity). Is it posible to not use "order by", cause this makes the query slow.

[item]

Hello,
orderBy is used and needed by many people .. so i can't imagine have a crm who don't order.

you order by what ?
have you think make index your field (orderBy) ?
maybe computer need more capacity ?

[yuri]

You need to create an index. https://docs.espocrm.com/development/db-indexes/

[TheHoff]

Hello,

I think the question of anuar was whether it would be possible to also have an option to have an entity Default Order field option of "none"; which would result in not having an order by clause in the SQL.
I happen to know the situation where anuars' question is coming from. So, let me elaborate the situation a little bit.

With a more complex query where you join multiple entities together, the ORDER BY clause in MySQL/MariaDB becomes a performance killer.

For instance, we have a query going over two entities with a JOIN.
- WITHOUT the order by it takes 0.0415 seconds to come back with 12.832 rows of data.
- WITH the order by takes 20.5730 seconds to come back with the same 12.832 rows of data.
And yes, there is an index on the order-field. And we've tried different indexes (single or multiple column indexes), without any significant change.

When I do an Explain of the query on the database, it shows "Using temporary; Using filesort" and no index is being used. When I do the query and give a hint to the database to use an index, it ignores it; most likely because of the use of a temporary table.

Currently the system has a mandatory order by field. It would be great of us to have the option to by-pass the order by for complex queries on large entities.

I hope this gives a bit of background to where the question is coming from :-)

[yuri]

W/o an order the result will be unexpected. Pagination will not work properly. MySQL is often pretty bad when it comes to optimization with orders when joins are used.

It's possible to force a specific index to be used in SelectManager. We used for email table for example

[espcrm]

Hi Hoff, how do we disable the order (temporary?) I would like to test how is the speed difference too.

Do you know if this is an issue only in v6.1 or beforehand as well?

[anuar]

You need to create an index. https://docs.espocrm.com/development/db-indexes/

We've already tried indexing, still slow

[TheHoff]

Hi all, just a quick update. We've upgraded the hardware and it works normal again.

[item]

Thanks TheHoff for give what you are updated in hardware.
i have post my hardware upgrade .. and HDD to SSD have increase speed by 4
and of course some index.

all basic entity have : join ... assignedUser, Teams.. out-of-box..

i have a table with 7000000 record with many related entity.. yes, it's slow (shared VPS) .. on night, he speed, on day he slow ..

[TheHoff]

Hi item , we've moved the database from a T3 to a M6g hardware instance (on Amazon AWS).