4 ms·

It depends also on what you want to prioritize. Indices add insertion overheads. You can have general rules for indices but ultimately it comes down to what ope

• Someone 4y ago
  Indices also can be extremely large. For example, a SELECT foo, bar, baz FROM quux WHERE foo = ?; will be fastest with an index on foo that also stores bar and baz (once the index entry is found, the engine won’t need to do a disk seek to the row data). If that table is large and you run SELECT foo FROM quux WHERE foo = ?; (Edited after zimpenfish’s remark about a copy-paste error) more frequently than the former query, just creating an index on foo alone may be the better choice. Whether it does depend on lots and lots of factors such as query distribution (is it worth it to make the monthly report twice as fast at the cost of running many other queries 1% slower?), disk speed (if you move a database to SSD you should revisit all indexing choices), and disk space. With advanced SQL engines, there’s way more choice. Maybe, it’s best to create an index on the first n character of a string field, or an index that only works for equality searches, not for phrases such as foo < 3, etc, or an index on only the data from last month (can be done in some databases with partitioned tables)
  • zimpenfish 4y ago
    > SELECT foo, bar, baz FROM quux WHERE foo = ?; > SELECT foo, bar, baz FROM quux WHERE foo = ?; Possibly a typo in the second query since the text indicates it should be different from the first?