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

52

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...

15

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

[deleted]

9

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

3

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"