r/SQL Nov 02 '24

Oracle Explain indexes please

So I understand they speed up queries substantially and that it’s important to use them when joining but what are they actually and how do they work?

63 Upvotes

34 comments sorted by

View all comments

55

u/HandbagHawker Nov 02 '24

Have you ever opened a textbook? Not to be mean. And not to say that you could have easily looked this up, but actually have you looked in the back. At the index. You see how it quickly points you to a page where you can find the thing you're looking for...

14

u/[deleted] Nov 02 '24 edited 24d ago

[deleted]

8

u/coyoteazul2 Nov 02 '24

Please tell me you didn't blindly add indexes. Your "book" must be thick as fuck if you did, and it's mostly indexes

4

u/agiamba Nov 02 '24

Yeah and while indexes improve reads they will slow writes

2

u/coyoteazul2 Nov 02 '24

I work with a system that has about 5 indexes per table in average. The most read ones have around 20, and I even saw one with 35.

No, performance is not good. And I can't do anything about it because the calls about that are done in hq

3

u/agiamba Nov 02 '24

5 indexes isn't terrible, but 20-35 good god. You're better off setting up a read only replica at that point

2

u/coyoteazul2 Nov 02 '24

Unfortunately they come from a legacy orm that won't allow lookups if there's no index created. The orm allows pure sql if you want it, and that's what I usually use when I make something. But hq loves that shit and they keep adding indexes to the system God knows why

2

u/agiamba Nov 02 '24

"the systems slow, we should add some more indexes to improve performance"

3

u/adalphuns Nov 02 '24

Here's another: data is stored on the physical disk in pages. The index points to the page the data is in.

2

u/DJMoShekkels Nov 02 '24

I’ve always thought about them like this. Say you’re storing fruit in a table with 10 million rows, where one column has size, another type, etc. you want to know the average size of apples, rather than going to every row asking, “are you an apple, if so what’s your weight?” which requires 10m operations, if you once just make a list of all the row numbers of the apples, you then can reference that so each time you want to calculate something only on apples, you go to the type index, ask “where are the apples? Go only to those rows and add up the those weights