Performance issue (suggestions on database indexes)

[Mike-the-one]

I am having an issue with performance. Let's give the following example, I have an orders table, and has the following fields:

user, order-date,order-status,order-customer-name.

Total orders in the table is about 6 millions.

One user (let's say user 1) has about 150,000 orders.

datatablerepository.findAll(spec1, spec2) takes 4 minutes to return the result.

(spec1 is the additional filter, such as data range, order status etc, and the spec2 is the prefilter, which is the userid.

I already have individual indexes created for this table.

The findAll method returns only the size of page (e.g. 25 records), but it took 4 minutes if the server has no load. If the server is busy, it takes more then 5 minutes, then the db connection is interrupted. I assuming the reason it takes this long is because of sorting, the default sorting is by order-date descending.

How can I resolve the performance problem?

One way I can think of is to create composite indexes. However I am not sure how many indexes I need to create.

For example, if in the UI I don't specify any keywords, then the spec1 should not add any predicates, then only the prefilter is effective, which is by the user. So if I added a composite index combines user and date will this help? or a composite index of date-user?

If I introduce order status in the search, then what kind of composite index I should create?

Thanks

[Mike-the-one]

I logged the query, without the order by, the query took a few seconds, with order by, it took a few minutes.

My table has an index with datetime column, and if I add force index to the query then it takes less then 10 seconds, so is there a way to add force index?

Or, how to create a index so that the query can use it by defualt?

thanks

[darrachequesne]

Hi!

the spec2 is the prefilter, which is the userid. the default sorting is by order-date descending.

Without any filter, a composite index on [userid, order-date] should work well, isn't that the case?

With a filter or an order on another column, it gets trickier. That being said, you should be able to reduce the number of possible indexes with:

• orderable: false for columns that can't be used as an order
• searchable: false for columns that shouldn't be searched on the server-side

You could have for example:

• one index on [userid, order-date]
• one index on [userid, order-status, order-date]
• one index on [userid, order-customer-name] for full text search (the order-customer-name column being the only one with searchable: true)

Another trick is to include all possibilities of an enum (i.e. "order-status") in your search by default:

myTable.column("order-status:name").search("PAYMENT_NEEDED+PAYED+SHIPPED+").draw();

So that there is always a WHERE order_status IN [...] clause in the generated SQL query.

[Mike-the-one]

Thanks.

Without any filter, a composite index on [userid, order-date] should work well, isn't that the case?

Unfortunately, this is not the case.

I should be more clear. The user id is a foreign key of another table, I also have a store foreign key which takes into consideration. This is query part:

select
        *
    from
        orders
    inner join
    stores
            on orders.orders_store_fk=stores.store_id
    where
        1=1
        and store.hidden=0
        and orders.user_id_fk=123
    order by
    orders.datetime desc limit 25;

And explain output shows the all the keys in possible keys, but the key used in the query from orders table is just the orders.orders_store_fk.

[darrachequesne]

@Mike-the-one did you find a solution? With the INNER JOIN clause, I guess you'll have to include orders_store_fk in the index too, right?

[Mike-the-one]

Hi, @darrachequesne yes, optimized the table helped. However, I have not find any solution to force use specific index in the query.

thanks

[darrachequesne]

As far as I'm aware, JPA does not allow one to specify the index to use, this is only possible for native queries.

[darrachequesne]

I think this can be closed now. Please reopen if needed.