4 ms·

If the data is small, then the index will also be small, so it doesn't really matter either way. The reason to avoid indexes is if you can't accept the time or

If the data is small, then the index will also be small, so it doesn't really matter either way. The reason to avoid indexes is if you can't accept the time or space cost. Only the last of OP's examples is a situation where choosing an index causes a problem; the rest are examples where an index isn't necessary but does not cause problems. Several examples are so trivial that you'd be crazy to use an external database at all. I'm not sure I'm really sold here.

deepsun 3y ago
Unless the small data changes often -- then DBMS needs to change both data and each index.
- electroly 3y ago
  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 large number of indices on a small table can still get you in trouble.
  - deepsun 3y ago
    > 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.
    - electroly 3y ago
      Wow! You're right; I wasn't considering that anyone would try to have that many indices. 100 is a lot. I should qualify my statement and say that you won't get in trouble with one index on a small table. I've edited my post accordingly.
      - sumtechguy 3y ago
        In those sort of cases your schema is probably not the correct pattern and needs to be reviewed. I had one proj where they had 40 indexes on one table. Inserts were terrible for that table. No one wanted to touch it as it happened to be the table where everything was at. They started pulling out 'nosql' sharding and other sorts of tools to fix it. Anything to not change that table with 200+ columns and 30 indexes and the flotilla of stored procs to go with it. They even ported it from MSSQL to Oracle. Thinking somehow magically oracle was going to make the poor schema run faster.
  - tomnipotent 3y ago
    > By definition, the changes will also be small Even if all you're updating is an int32 column from 1 to 2, the RDBMS is still writing much more data than 4 bytes. At a minimum it's making a copy of the full page the data is in prior to the update (so 8k for pg, 16k for mysql) and usually multiples of that (mysql writes both before/after).
fnordpiglet 3y ago
In the case i mention above of log data where data access patterns dominate storage without retrieval computing indices up front is absurdly expensive and challenging at extreme scales. In that case minimal time and perhaps probabilistic indices computed on ingestion and extremely parallel brute force search on search works out both in terms of unbounded scale and cost - by like one to two orders of magnitude.
- electroly 3y ago
  Indeed, we also use an unindexed store for our logs (having migrated to that from a system that was indexed). This is clearly not the "data is so trivially small that nothing matters" situation that the OP is describing in 4 of the 5 of their examples. I hope you can see this isn't at all incompatible with my post.
  - fnordpiglet 3y ago
    I can. I’m just pointing out there are definitely cases where indexing sucks. I agree the original post doesn’t touch those cases.
jefftk 3y ago
In cases where your data is small enough that you don't need an index, an index is adding technical complexity. In some cases that will be small (you are already using a database and it has built-in support for the kind of index you need) and then, sure, go ahead. But in other cases it will not be so small (you can't get indexes without switching to a more complicated tool, you'd need a somewhat unusual kind of index which requires a plugin, etc) and it's worth thinking about whether your system will be more reliable and low-maintenance long-term if you go without.
- electroly 3y ago
  I think we're in agreement here; this is the part where I said "Several examples are so trivial that you'd be crazy to use an external database at all". For your shell history, you not only don't need indexes, you don't need the database either (as you conclude in the post). But if you're already in the database, it falls into that first "the index feature is right there" category. This HN thread is full of people skipping the index on their SQL tables in situations where I would write the index. Mostly I think people are just missing that you need to write indices in response to queries rather than what you're imagining when you first create the table.

deepsun 3y ago

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

electroly 3y ago
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 large number of indices on a small table can still get you in trouble.
- deepsun 3y ago
  > 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.
  - electroly 3y ago
    Wow! You're right; I wasn't considering that anyone would try to have that many indices. 100 is a lot. I should qualify my statement and say that you won't get in trouble with one index on a small table. I've edited my post accordingly.
    - sumtechguy 3y ago
      In those sort of cases your schema is probably not the correct pattern and needs to be reviewed. I had one proj where they had 40 indexes on one table. Inserts were terrible for that table. No one wanted to touch it as it happened to be the table where everything was at. They started pulling out 'nosql' sharding and other sorts of tools to fix it. Anything to not change that table with 200+ columns and 30 indexes and the flotilla of stored procs to go with it. They even ported it from MSSQL to Oracle. Thinking somehow magically oracle was going to make the poor schema run faster.
- tomnipotent 3y ago
  > By definition, the changes will also be small Even if all you're updating is an int32 column from 1 to 2, the RDBMS is still writing much more data than 4 bytes. At a minimum it's making a copy of the full page the data is in prior to the update (so 8k for pg, 16k for mysql) and usually multiples of that (mysql writes both before/after).

fnordpiglet 3y ago

In the case i mention above of log data where data access patterns dominate storage without retrieval computing indices up front is absurdly expensive and challenging at extreme scales. In that case minimal time and perhaps probabilistic indices computed on ingestion and extremely parallel brute force search on search works out both in terms of unbounded scale and cost - by like one to two orders of magnitude.

electroly 3y ago
Indeed, we also use an unindexed store for our logs (having migrated to that from a system that was indexed). This is clearly not the "data is so trivially small that nothing matters" situation that the OP is describing in 4 of the 5 of their examples. I hope you can see this isn't at all incompatible with my post.
- fnordpiglet 3y ago
  I can. I’m just pointing out there are definitely cases where indexing sucks. I agree the original post doesn’t touch those cases.

jefftk 3y ago

In cases where your data is small enough that you don't need an index, an index is adding technical complexity. In some cases that will be small (you are already using a database and it has built-in support for the kind of index you need) and then, sure, go ahead. But in other cases it will not be so small (you can't get indexes without switching to a more complicated tool, you'd need a somewhat unusual kind of index which requires a plugin, etc) and it's worth thinking about whether your system will be more reliable and low-maintenance long-term if you go without.

electroly 3y ago
I think we're in agreement here; this is the part where I said "Several examples are so trivial that you'd be crazy to use an external database at all". For your shell history, you not only don't need indexes, you don't need the database either (as you conclude in the post). But if you're already in the database, it falls into that first "the index feature is right there" category. This HN thread is full of people skipping the index on their SQL tables in situations where I would write the index. Mostly I think people are just missing that you need to write indices in response to queries rather than what you're imagining when you first create the table.