11 months ago

Slow query when using string instead of integer (implicit conversions)

Posted 11 months ago by booni3

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?

Please sign in or create an account to participate in this conversation.