Multiple Filters from Multiple tables slam aggregated results

Good day.

I'm on 0.44.3. I have a dashboard with three questions - two use pivot tables to display results by month and week, respectively. The third just returns the raw data which people can then click on the question to download the results, if desired, or scroll through the rows.

The primary table is one of daily sales. It contains the posted date, fiscal period, account number, ISBN and the net totals of units and dollars for that customer, for that ISBN, on that particular day. For example, if Amazon placed several different purchase orders for the same ISBN on the same day, there would only be one record in the daily sales table which would be aggregated totals for all of the orders.

The daily sales points to the customers table via the account number, and the titles table via the ISBN.
The dashboard I've made gives the option for the user to select various attributes from both the customers and titles table to be applied to the results, as well as the date range for sales o be considered. We have history going back to fiscal 2017, so as I write this the daily sales has 693,141 rows, titles 18,371 and customers 104,389.

All works well as long as a person selects attributes in the filters from one of the joined tables (customers or titles). Once a person adds attributes from the second table, the raw rows of data update almost immediately, but the pivot table total questions get lost and eventually time out. All of the attributes that are available are indexed, and we're on MySQL 8.x.

One idea I've had is to create a series of filtering dashboards - first select the time range you want, then use the results of that as the basis for selecting title attributes (if any), then use those results to apply any customer attribute selection on. Not sure how to set all that up, or if it's even a viable way to go.

Any suggestions appreciated as I feel I'm ** that close ** to figuring it out, but am missing something.

Hi @plg6
I'm not sure I follow "adds attributes from the second table" - you mean that columns on the joined table are selected in filtering?

If you are using Pivot Table, then there's a lot of calculations done underneath (queries executed), which are needed to calculate the sub-totals, but perhaps there isn't any indexes on those columns, which cause the entire query to timeout?

Try looking in your database query log, so you can see which queries are slow or missing indexing.