Bump

Primary keys, in a lot of the DBMSs, are clustered indexes by default, while indexes created via CREATE INDEX are typically non-clustered indexes unless explicitly specified.

Google those terms to learn more about it, but the short version is that the query optimizer has determined that using a clustered index + index scan is more efficient than using a non-clustered index + RID lookup for this specific query.

Now, depending on which DBMS you are using, it is possible to "force"/"hint" the DB to use a particular index in the SELECT statement. However, this is usually for handling edge cases and I personally don't think this is a good idea in your case.

I’m very confused then about composite index’s then

How would I optimize this query then?

Because it’s going to have to go through way more rows from how I understand it if it’s going to use a single key

Well…

I guess now that I think about it

I figured if the expires at and remaining were apart of the key it would be able to find it faster

But at the end of the day the query just needs to find the single entry first for id and then perform those other where clause operations on it

I guess if the where clause for Id = x was something like greater than or less than

Then the composite key would be more important?

Since it now becomes a range of values

I just realized this would just make it less important since composite index’s stop at the first range lmao

Logically this is true, but we also need to consider the fact that accessing various pointers will also introduce overheads.

The query optimizer figured that out and decided to use the primary key index because it has less overheads, and results in shorter computation time, despite needing to do a partial index scan.

Do ping me if you wish to know the details. But do note that this is heavily related to how RDBMS works and is typically DBA's domain.

Yeah I get this is super complicated lmao

And I am honestly pre-optimizing

I have no real need to be doing this stuff now

I just kind of want to be ahead of the game since I’m building a marketplace

So it’s going to have high amount of traffic

BTW, this is not necessarily true.

This some times happens for similar reason as I explained. The DB believes the overhead of going back and forth between the index and the actual table isn't worth it, and decided to just scan the table regardless

I’m just reading planetscale blog posts about composite index’s

And it says it stops after the first range column is used

So if the composite key DID work it would only use id and expires at

Have a link? Interested to know more about

Just on the bottom

Click the previous post until you see composite index’s

I also may have had a redundant index

After reading this page too

Ah, interesting, this seems to be MySQL's unique behavior. I do cross check with MySQL's doc just in case

I’m going to head to sleep now

Thanks for your support