- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
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 ...
Admin
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.
Admin
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.
Admin
Your database is a disaster, you're waxin' your modem, tryin' to make it go faster!
Admin
I'm loving it. We need more poetry around here.
Admin
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.
Admin
I see you are a person of taste.
Admin
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.
Admin
No. Indexes are just implementation details.
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 nullattribute. 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.Admin
Or just add a
UNIQUEconstraint when defining the table...Admin
That creates an index as an implementation side-effect. He seems to have been shooting for index-free ways to implement the functionality.
Admin
Huh. OK. TIL. Thanks.
Admin
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)
Admin
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.
Admin
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.
Admin
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.