Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Cases where full scans are better than indexes

jefftk.com

Post reply on HN
Print view View original

41–50 of 226 posts

Re: Cases where full scans are better than indexes

#41

Post by deepsun »

electroly wrote: post #14
deepsun wrote: post #10

Earlier quoted context omitted.

Unless the small data changes often -- then DBMS needs to change both data and each index.

By definition, the changes will also be small. It's just not possible for it to blow up in a way that would cause a problem, because we're only talking about tiny tables. There's nothing you can do in a single[1] index for a table with 350 rows that will cause any heartache vs. an unindexed table with 350 rows. 4 of the 5 examples in the article are just "this table is too small to care." [1] Edited per discussion; a…

> There's nothing you can do in an index for a table with 350 rows

I meant if you have 100s of indices -- it might be slow even on small tables.

I actually had a production issues from using a lot of indices, but it's not apples-to-apples with current discussion, because the table sizes, DBMS and update rates were much larger, fixed by removing indices and splitting table into multiple.

Re: Cases where full scans are better than indexes

#42

Post by pc86 »

eterm wrote: post #39
raldi wrote: post #2

When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary. It hadn’t happened by the time I left the company, even though sales were exceeding expectations.

That goes against my intuition, because the performance would be more impacted (beneficial to have an index) where you have very few bits set on a boolean field. If you have 10m users and 100 have "IsPremium = 1" then an index massively speeds up anything with WHERE IsPremium = 1, compared to if the data is fairly evenly spread between IsPremium = 1 and IsPremium = 0 where an index won't be much benefit. So low sales…

You wouldn't need any of these WHERE clauses if you have all the premium users in one table. Even managing the users in both tables in pretty trivial when you just add someone as they pay and remove them as the premium account expires.

Re: Cases where full scans are better than indexes

#43

Post by [deleted] »

eterm wrote: post #39
raldi wrote: post #2

When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary. It hadn’t happened by the time I left the company, even though sales were exceeding expectations.

That goes against my intuition, because the performance would be more impacted (beneficial to have an index) where you have very few bits set on a boolean field. If you have 10m users and 100 have "IsPremium = 1" then an index massively speeds up anything with WHERE IsPremium = 1, compared to if the data is fairly evenly spread between IsPremium = 1 and IsPremium = 0 where an index won't be much benefit. So low sales…

[deleted]

Re: Cases where full scans are better than indexes

#44

Post by wswope »

raldi wrote: post #31
cactusplant7374 wrote: post #15

Earlier quoted context omitted.

Indexes are such an easy thing to add. I don't get it. Seems like under optimization when you consider the tradeoffs.

If you have nothing to optimize yet, how will you know if you’re optimizing it right?

Typically, you create a large number of test records and see how your expected access patterns perform.

Re: Cases where full scans are better than indexes

#45

Post by abtinf »

eterm wrote: post #39
raldi wrote: post #2

When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary. It hadn’t happened by the time I left the company, even though sales were exceeding expectations.

That goes against my intuition, because the performance would be more impacted (beneficial to have an index) where you have very few bits set on a boolean field. If you have 10m users and 100 have "IsPremium = 1" then an index massively speeds up anything with WHERE IsPremium = 1, compared to if the data is fairly evenly spread between IsPremium = 1 and IsPremium = 0 where an index won't be much benefit. So low sales…

> paying-user table index-free

Presumably, that means they created a new table intended to be straight-joined back to the user table. No need to search a column.

Re: Cases where full scans are better than indexes

#46

Post by magicalhippo »

themerone wrote: post #34
jameshart wrote: post #22

“We’ll add an index when it gets slow” is saying “screw the guy who’s on call the night this system starts timing out”. Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering. If you deliberately aren’t implementing indexing, know at what point indexing would begin to matter. Put in place guard rails so the system doesn’t just blow up unexpectedly. Th…

Usually things will slow down gradually or fail right away under production loads.

Unless production loads are non-uniform in time, think Black Friday or similar.

Re: Cases where full scans are better than indexes

#47

Post by giraffe_lady »

pyrophane wrote: post #29

I think the risk of creating indexes prematurely is really that you will make assumptions about how your table is going to be queried that don't turn out to be correct, so you create indexes that you will never need, even if the table gets large. That probably isn't the end of the world, but it can make it harder for others to understand what is going on in your database (indexes can also be a form of documentation)…

This has matched my experience too. Most experienced developers can probably identify an inappropriately hot table and add an index to it. Where a DB with dozens of individually modest indexes that add up to be huge is much trickier and more labor intensive to confidently improve.

Re: Cases where full scans are better than indexes

#48

Post by raldi »

eterm wrote: post #39
raldi wrote: post #2

When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary. It hadn’t happened by the time I left the company, even though sales were exceeding expectations.

That goes against my intuition, because the performance would be more impacted (beneficial to have an index) where you have very few bits set on a boolean field. If you have 10m users and 100 have "IsPremium = 1" then an index massively speeds up anything with WHERE IsPremium = 1, compared to if the data is fairly evenly spread between IsPremium = 1 and IsPremium = 0 where an index won't be much benefit. So low sales…

We had lots of indexes on the user table; there was a separate table with details of paying users.

Re: Cases where full scans are better than indexes

#49

Post by magicalhippo »

captainmuon wrote: post #25

The inverse is also true, often you have linear or even quadratic lookup times when something like an index can give you the result "immediately". I wish languages other than SQL had a concept of adding an index. Imagine a variant of Python's list or C++'s vector where you can add indicies on arbitrary fields of the contents, like a more flexible dict. Something like (pseudocode) books = List and then you could up by…

For C++ there's Boost's multi_index[1].

[1]: https://www.boost.org/doc/libs/1_82_0/libs/multi_index/doc/i...

Re: Cases where full scans are better than indexes

#50

Post by AnotherGoodName »

ydnaclementine wrote: post #19

What is the rule that you use on which fields to index? I've been following the general when adding fields: if the field is mentioned in a sql WHERE, ORDER BY, or GROUP BY EDIT: JOINs

Don't overthink that part. Just rely on EXPLAIN. It will tell you if the query had an index to use or if it scanned. You'd be surprised when a DB does/doesn't use an index if you try to figure it out yourself.

A run book for any org when writing queries should be

1) Write the query

2) Before shipping the query run the query through EXPLAIN. Did it scan or did it use an index?

3) If it scanned can you re-write it to use an existing index (the usual answer honestly).

4) Create the index.

Post reply on HN