I’m working on a Laravel 12 project running MySQL 8.4. I have various models like Buyer, BuyerTier, Application and PingtreeGroup and I want to store raw transactional models in my PingtreeTransaction model.
This table will store around 500,000 entries a day, it’s schema, minus indexing looks like:
Schema::create('pingtree_transactions', function (Blueprint $table) {
$table->ulid('id');
$table->foreignId('company_id');
$table->foreignId('application_id');
$table->foreignId('buyer_id');
$table->foreignId('buyer_tier_id');
$table->foreignId('pingtree_group_id');
$table->foreignId('pingtree_id');
$table->mediumInteger('processing_duration')->default(0);
$table->smallInteger('request_response_code')->default(200);
$table->decimal('commission', 8, 2)->default(0.00);
$table->string('result', 32)->default('unknown');
$table->string('request_url')->nullable();
$table->integer('partition_id');
$table->date('processed_on');
$table->dateTime('processing_started_at');
$table->dateTime('processing_ended_at')->nullable();
$table->timestamps();
$table->primary(['id', 'partition_id']);
});
The various query use cases are as follows for joining transactions to models are:
- Fetch all pingtree transactions for any given application
- Fetch all pingtree transactions for any given application between two dates
- Fetch all pingtree transactions for any given buyer
- Fetch all pingtree transactions for any given buyer between two dates
- etc…
But then, there’s a front-end page that’s paginated and shows a date/time picker along with a tags component for each model allowing a user to filter all transactions, for example:
- Show me all pingtree transactions for the past 3 days where the
Buyer is either “foo” or “Bar”, and where the BuyerTier is “a” and “b” where the result is either “accepted” or “declined” on any of them.
A user might not always include all fields in their search for models, they might only want to see everything over a period minus specific models.
For the end user, there’s a lot of possible combinations for reporting via this front-end page since this is a business choice.
So in summary, there’s two cases:
- Individual model joining
- A report page with various filters
Indexing dilemas…
Since I want to join individual models which won’t require a date, like the foreignId columns, I would’ve thought adding the following indexes are suitable:
$table->index(['application_id']);
$table->index(['processed_on']);
$table->index(['company_id']);
$table->index(['application_id']);
$table->index(['buyer_id']);
$table->index(['buyer_tier_id']);
$table->index(['result']);
$table->index(['partition_id']);
$table->index(['processed_on']);
$table->index(['processing_started_at']);
$table->index(['processing_ended_at']);
On a table with millions of rows, adding new indexes is going to lock the table, but, the issue above, is now because I don’t have a composite index, and the dates are ranges, the cardinality is really high on those columns, and lower on the buyer and buyer tier columns, so the database ends up weirdly just picking one index for processing_started_at which ends up taking minutes to load.
explain select
*
from
`pingtree_transactions`
where
`company_id` in (2, 1)
and `buyer_id` in ("154", "172")
and `buyer_tier_id` in ("652")
and `processing_started_at` >= '2025-05-21 23:00:00'
and `processing_ended_at` <= '2025-05-23 22:59:59'
and `result` in ("accepted")
order by
`processing_started_at` desc
limit
26 offset 0
If I then add some composite index with multiple columns in there like:
$table->index([
'company_id',
'buyer_tier_id',
'buyer_id',
'result',
'processing_started_at',
'processing_ended_at'
], 'composite_pingtree_transactions_all_index');
Then it only appears to use it if all of the columns are in the search query and is incredibly fast at around 5ms, but given the various combinations in filtering, this would then seemingly bloat the database with all the combinations, and if one field is missed out, it ends up falling back to a sub-optimal index.
Essentially, what combination of indexes then would best to always utilise indexing?
The reason for adding:
$table->primary(['id', 'partition_id']);
Is because I’m experimenting with partitioning, and partition_id would house the current day in YYYYMMDD format, so there would be a partition for each day, but when trying this, and adding partition id into the query it seems to use partition pruning but no indexing.
So the question here is, what indexing should I add for my use cases defined above.
