Level 75
You can speed up even more by just counting one field
select count('id')....
Jun 10, 2019
3
Level 3
Slow query when using string instead of integer (implicit conversions)
I have two almost identical queries.
select count(*) from `master_transactions` where `master_transactions`.`settlement_id` = '11839491331' and `master_transactions`.`settlement_id` is not null and `settlement_type` = 'amazon_settlement' and `sales_clearing_invoice_id` is null
select count(*) from `master_transactions` where `master_transactions`.`settlement_id` = 11839491331 and `master_transactions`.`settlement_id` is not null and `settlement_type` = 'amazon_settlement' and `sales_clearing_invoice_id` is null
The top one runs in 18ms... and the bottom one runs in 400ms. Why?!
This is all caused by adding or removing single quotes to the settlement_id column. The settlement_id column is a varchar 255 column within my database, and is indexed.
When searching with a string the index is used. When searching with an integer the index is not. This query is being automatically created via a belongs to relationship. Is there a way to force it to use a string instead of integer and why does it make so much difference?
Level 3
@jlrdw I tried that also once I realised the default ->count() query builder function in laravel does this. It makes minimal difference on this query - a few ms.
However, changing from a string to an integer makes a several thousand percent improvement.
Level 3
This is down to implicit conversions. The settlement-id on the amazon table is a bigint (as that is what this source provides) and on the master transactions it is a varchar (because there are multiple formats stored here).
Doing a where `settlement-id` (bigint) = `settlement_id` (varchar) is known as an implicit conversion and has a huge performance hit.
https://hackernoon.com/are-implicit-conversions-killing-your-sql-query-performance-70961e547f11
Please or to participate in this conversation.