• Hasseman (unregistered)

    Many years a go I was asked to look at a system that went from acceptable performance to very bad within a day or two a few month after taking into production. Seam as long the tables fitted within main memory the performance was acceptable. Adding indexes did a lot ...

  • Sauron (unregistered)

    A relational database with no index... Wouldn't that be a non-relational database?

    That's a big WTF. Even aside from performance, so many things can just go wrong. There the id field could be empty for some rows. Or several rows could have the same id value.

  • MaxiTB (unregistered)

    Well, at least the is not a functional statement part of the conditional expressions. I have seen enough DBs with dozen of indexes and none of them every getting used because the queries force full-table scans anyway.

  • trainbrain27 (unregistered)

    Your database is a disaster, you're waxin' your modem, tryin' to make it go faster!

  • (nodebb) in reply to trainbrain27

    I'm loving it. We need more poetry around here.

  • (nodebb) in reply to Sauron

    A relational database with no index... Wouldn't that be a non-relational database?

    No. A non-relational database is a spreadsheet. Or an RDBMS where no two tables are related in any way so they are "fancy" spreadsheets stored in a database rather than in flat files.

    WTF #1: No indexes. WTF #2: This probably should be a view with a descending index (WTF #1 again) to make the row selection (WTF #3) more efficient.

  • (nodebb) in reply to trainbrain27

    I see you are a person of taste.

  • (nodebb)

    I was asked to do a code review of a site that was running poorly. I asked for a specific example and traced the API code path. It looked fine and there was nothing that would slow things down except a database query. They hadn't given me the database schema so I was suspicious. Called the dev up, had them screen share SSMS or whatever, and wouldn't you know it, no keys or indicies in the database of any kind.

  • (nodebb) in reply to Sauron

    A relational database with no index... Wouldn't that be a non-relational database?

    No. Indexes are just implementation details.

    There the id field could be empty for some rows. Or several rows could have the same id value.

    You could implement those constraint without indexes. It's easy to check the columns you are inserting for null values where the column has a not null attribute. It's easy to check if the id you are inserting is unique within the table, just by doing a linear search through all the rows to see if has been used previously. You can always obviate the index, but the alternative usually involves a linear search through all the rows, or for joins, a lot of linear searches through all the rows.

  • (nodebb)

    It's easy to check if the id you are inserting is unique within the table, just by doing a linear search through all the rows to see if has been used previously.

    Or just add a UNIQUE constraint when defining the table...

  • (nodebb) in reply to Steve_The_Cynic

    Or just add a UNIQUE constraint when defining the table...

    That creates an index as an implementation side-effect. He seems to have been shooting for index-free ways to implement the functionality.

  • (nodebb) in reply to Jaime

    Huh. OK. TIL. Thanks.

  • SheriffFatman (unregistered) in reply to Bananafish

    A non-relational database is a spreadsheet. Or an RDBMS where no two tables are related in any way so they are "fancy" spreadsheets stored in a database rather than in flat files.

    The "relational" in RDBMS does not refer to relationships between tables, but to the concept of a relation -- a set of tuples where the corresponding values in each tuple are drawn from the same domain. So the first value in each tuple might be a positive integer, the second a complex number, the third a text string, etc. Generally a relation will express a predicate -- e.g. "Y is the square of X", or "Person A thinks that Person B likes Person C".

    An RDBMS database consisting entirely of unrelated tables, or even just a single table, is still relational.

    https://en.wikipedia.org/wiki/Finitary_relation https://en.wikipedia.org/wiki/Relation_(database)

  • (nodebb)

    Always a flip side. If a table has very high insert/delete rates and small query rates - then adding an index can really HURT performance. Have seen this happen more than once over the decades.

  • Fizzlecist (unregistered) in reply to TheCPUWizard

    Adding too many indexes can have a similar impact on performance. We had an issue with query performance at a previous job. All of the tables used in the query had indexes, with most of them being compound indexes. We finally discovered that the query was doing table scans instead of using the compound indexes due to the order of the columns in the indexes - the first column in the index wasn't in the query so the index wasn't being used. Changing the order of the columns in the index brought the average query time from 20+minutes down to a few seconds.

  • ParityTheUnicorn (unregistered)
    Comment held for moderation.
  • Lei (unregistered) in reply to Fizzlecist

    Taking over responsibility for a ~12TB Microsoft SQL-Server database I found out that one table was 500GB in size. The previous owner had run into performance issues and had created 4 indexes on the table, on different columns. And then on each of the indexes he had included every column in the table, effectively maintaining 5 copies of the table with no real benefit other than slowing down writes as well :-/ Sometimes I wish I worked with shelving tomatoes in the supermarket.

Leave a comment on “SQL with no Equal”

Log In or post as a guest

Replying to comment #:

« Return to Article