0

I want to get min user_id for a tenant_id.

mysql engine - innodb

table - users(id, tenant_id, is_deleted, creation_date)

pk (id)

sk (tenant_id, creation_date)

query1 select min(id) from users where tenant_id = 123;

enter image description here

query2 select id from users where tenant_id = 123 order by id limit 1;

enter image description here

I have observed that the query1 is faster than query2. Also, If I force index on query2, then it uses filesort in addition to using index. I want to know why query1 is faster and in query2 why filesort is being used as innodb adds pk at end of sk.

0

1 Answer 1

1

First query. Server filters the rows by provided tenant_id value using the index then investigates hidden primary key expression which is a part of any index and finds minimal id.

Second query. Server scans the whole table (due to primary key the rows in the table body are already sorted by id) until specified tenant_id found.

The index by (tenant_id, id) seems to be the most efficient. - Akina

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.