I’m executing the below code on a collection which holds the purchased tickets for each event (event id is a reference field within this collection).
I’ve added a console.time() and it shows that this takes around 0.5 sec. Seems to me a very poor performance as I need to perform additional queries in order to return a valuable result to the end user and the total takes around 1.5 sec.
I’ve come to know about this since the site monitoring show timeouts when invoking the API.
Is there a way to improve the performance for this kind of query? Will it help to replace the reference field with a regular field which will hold the event id?
const filter = wixData . filter (). eq ( “eventId” , eventId );
//const having = wixData.filter().gt(“maxPopulation”, 1000000);
// .filter(filter)
return wixData . aggregate ( “MyTickets” )
. filter ( filter )
. group ( “ticketId” )
. count ()
. run ()
. then (( results ) => {
if ( results . items . length > 0 ) {
console . log ( "items " + JSON . stringify ( results . items ))
console . timeEnd ( “getTicketsSum” );
// let items = results.items;
// let numItems = results.length;
// let hasNext = results.hasNext();
}
resolve ( results );
})
. catch (( error ) => {
let errorMsg = error . message ;
let code = error . code ;
reject ( error )
});
Depending on the size of your dataset and filter, aggregate can be an expensive operation, on any database
Without a filter, it is a full table scan
With a filter, if you do not have an index, it is also a full table scan
Wix offers 3 indexes on regular plans, and using the new Business VIP (being rolled out now) plan you get a dedicated collection + 32 indexes - which can improve your performance.
Another alternative is to prepare the aggregates before hand using scheduler, as another collection of pre-aggregated values, which you just query directly instead of doing the aggregate
I thought each collection has an index on the _id field and on reference fields (which are also id type field), this is not the case?
What does mean 3 indexes, is it 3 indexes for all the collections? can I choose which field will be indexed?
Btw, this specific collection is not big, it is around 1000 rows.
Hello. To answer some of your questions…
Yes there are indexes on all collections ootb which are:
All Wix collections are created with 2 indexes:
as referenced from this article: https://support.wix.com/en/article/velo-indexes-and-wix-data-collections
You may create up to 3 regular (or 1 unique) indexes on your collection which you can also read more about at the above linked article.
Here is another article about how to set them up: https://support.wix.com/en/article/content-manager-adding-an-index-for-your-collection
The use of indexes should be taken carefully, it has a price in the creation of the item.
By the way, I would like to understand if it is possible to do .group(“_createdDate”)
Because the information contains, in addition to the day, also an hour and seconds, then the attempt to accumulate, for example, all the donations on the same day is ineffective.
The solution I see is to add a hook that will create another field with the new field type, which is just a date without a time. (big headache all these WIX changes)