The classic analogy for indexes goes ... databases are like libraries.
Tables are like books stored in a library.
Rows are stored on pages of a...
For further actions, you may consider blocking this person and/or reporting abuse
Keep in mind, that indexes are only useful if you have a high variance in your data, like username or firstname, lastname. Something that is limited in its variabce, like a enum field - category for example which holds only 5 possible categories isnt the best choice to add an index 😊👍
It's not always true. Selective index is good but if you not index on column in foreign key you may can get big problem with locks.
Can you explain this a little further? :)
Of course. For example table2 have column colF and foreign key to table1. If on colF not indexing and while table1 (any data) change we have exclusive table lock on table2. It's true for Oracle.
I have this problems many times.
Cool, thank for sharing! I guess its kind if database agnostic, good to know for oracle! ✌️😊
Great advice! That’s certainly what I do in the real world and more often than not it will be two or three columns not just one
yeah for that you can use look up table
That execution plan sounds like a great feature especially as it is nicely expressed as time. This would allow me to check whether my schema is optimized for some SQL requests, or simply to see if the SQL request structures I use are not too time consuming (that would be fine for my lab assessment, good grades come in handy!).
Do you know if this is available in other DBMS? Even if you wrote Oracle users weren't lucky here, I tried to check for that DBMS (because we are forced to use Oracle at my university) and I wasn't able to find a solution as easy as with SQL Server (expressed as seconds). Any solution for MySQL/MariaDB? If that matter, I'm used to use the CLI, but also GUI tools like DBeaver and SQL Developer.
Been a while, but I used to get the query execution plan in MySQL by simply putting
EXPLAINin front of it. I believe the same keyword is used in Postgres as well.Presumably it's the same for MSSQL, but MySQL allows you to create indexes across multiple columns. This is very useful in a lot of situations where the identifying feature of the data is a combination of two or more things, e.g. user ID and a friend ID, where you'll likely have a row for each of the ID links.
Nice catch! In the real world I’ll create indexes across more than one column, as you’ve described. But in the interests of simplicity kept it to just one in the examples
Great post, Helen. Really missing Execution Plan now that I use Postgres.
EXPLAINis... fine, but EP was so nice.Good to know... I’m migrating to Postgres so it’ll be a bit of a change
It’s more an aspect of your tools, I know data grip can do some impressive stuff, if you can afford it
This is great! One of the first things I did at my job was create indexes and that dramatically sped up our SSRS queries. It felt like a good low hanging fruit, which unfortunately gets overlooked.
That’s great to hear. Did you have a friendly DBA to talk it through with?
In this case, I also became a bit of the friendly DBA. It's a small in house team, so everyone does a bit of everything!
I feel like there are a lot of 'accidental DBAs' out there :)
That's probably true, and we're all just trying to be extra careful not to drop the database!
Having all that power is a beautiful... and terrifying thing :)
So if I’m (typically) putting measurements in a database with the Unix epoch time, and then pulling out the last 24 hours to plot, do I want to add an index on epoch? Is there an SQL “query” that’ll do that? I’m mostly using mysql and mariadb on Raspberry Pi (Debian-ish) if it matters. Thanks!
There's hardly anything to add to this awesome post, except maybe:
"I don't maintain the database; I just query it!" folks, don't be scared off!
Even if you don't have tools that show you the indexes, it's always good to ask your DBA (or some other expert) where the indexes are in tables you plan to join with SQL (or a drag-and-drop tool that generates SQL behind the scenes), so that you can potentially write faster queries.
I say "potentially" because the index won't always make your query run faster (it depends on the data and on your query). But that's the subject of a 3-hour university lecture and exercises doing "explain plan" with pencil and paper ... :-)
A good guess, if you're in a rush, is that the "primary key" for a table is often indexed to make "joining" it to other tables against "foreign keys" faster. In other words, join tables the way they seem intuitively related to each other, those joins may run reasonably quickly.
But ask your DBA or other database expert to be sure.
Thanks Katie :)
Great advice to check in with your DBA before making those kinds of decisions. There's often a lot more going on behind the scenes and other indexes may have been applied for different reasons. An index may make things go faster, but there could be another way to solve the problem.
What is the program you're using? I've never used anything that resembles those screenshots.
Just good old SQL Server standard
To be precise, the actual app in the screenshots looks like SQL Server Management Studio.
Thank you so much for this insight into Indexes. It's well written and I am going to be using this as a resource when I need to come back to it!
Thanks so much Aubrey :)
Cool
I'm definitely coming back to read this... I've read many articles about indexing and they just seem too technical forgetting the main point :\
Thankx Helen!
You’re most welcome :)
Another tip is make sargable queries to ensure that the DBMS engine will use indexes
Loved the way you used an example of library . That paragraph clarified overall purpose of article.
Thanks! Glad you found it useful
I use Oracle and was reading this just for reference, and then at the bottom, there's an Oracle resource. Great article Helen!
Thanks Ross! There’s so much good stuff here. I’m glad I can build on the posts that have been written already.
Good post but indexes make the writes slow too :)