5 ms·

Yeah, not a big deal but completely useless nonetheless as you would never really query your table for just the one decimal column (eg the price) but a couple m

by rudasn 1y ago

Yeah, not a big deal but completely useless nonetheless as you would never really query your table for just the one decimal column (eg the price) but a couple more (eg the category and the price) so you'd have a multi-column index on those columns. The index on just the price column never gets used.

cwbriscoe 1y ago
What if you wanted to select "top 100 most expensive products" or number of products between $0.01 and $10, $10.01 and $100, $100.01 and $1000? Sure you could do a full table scan on your products table on both queries but an index on price would speed both queries up a lot if you have a lot of products. Of course you have to determine if the index would be used enough to make up for the extra time on index update when the price changes or products are added or deleted.
- erulabs 1y ago
  Cheap solution, sure, add an index. But you're asking an OLAP question question of an OLTP system. Questions like that are best asked at least of an out-of-production read replica or better an analytics db.
  - robertlagrant 1y ago
    I don't really understand this - what is an out of production read replica? Why wouldn't it just go to a production read replica? And what is an "analytics db" in this context?
    - gkalin59 1y ago
      You stream CDC events to have a 1 to 1 read replica in something like Snowflake/Databricks where you can run all kinds of OLAP workflows on this analytics db replica.
      - robertlagrant 1y ago
        Oh, sure, but wouldn't the whole website be served out of a read-friendly database? Why would you have a separate "analytics" database to the main database(s) driving the site?
    - erulabs 1y ago
      In general just avoiding mixed types of load. Predictable, audited application queries in a user request shouldn’t be mixed with potentially extremely expensive long running analytics queries. Different replica sets isolates customers from potential performance impacts caused by data analytics.
  - cwbriscoe 1y ago
    In the real world, people want cheap solutions and they want it yesterday.

by rudasn 1y ago

cwbriscoe 1y ago

What if you wanted to select "top 100 most expensive products" or number of products between $0.01 and $10, $10.01 and $100, $100.01 and $1000? Sure you could do a full table scan on your products table on both queries but an index on price would speed both queries up a lot if you have a lot of products. Of course you have to determine if the index would be used enough to make up for the extra time on index update when the price changes or products are added or deleted.

erulabs 1y ago
Cheap solution, sure, add an index. But you're asking an OLAP question question of an OLTP system. Questions like that are best asked at least of an out-of-production read replica or better an analytics db.
- robertlagrant 1y ago
  I don't really understand this - what is an out of production read replica? Why wouldn't it just go to a production read replica? And what is an "analytics db" in this context?
  - gkalin59 1y ago
    You stream CDC events to have a 1 to 1 read replica in something like Snowflake/Databricks where you can run all kinds of OLAP workflows on this analytics db replica.
    - robertlagrant 1y ago
      Oh, sure, but wouldn't the whole website be served out of a read-friendly database? Why would you have a separate "analytics" database to the main database(s) driving the site?
  - erulabs 1y ago
    In general just avoiding mixed types of load. Predictable, audited application queries in a user request shouldn’t be mixed with potentially extremely expensive long running analytics queries. Different replica sets isolates customers from potential performance impacts caused by data analytics.
- cwbriscoe 1y ago
  In the real world, people want cheap solutions and they want it yesterday.