Good article for beginners, where everything is well explained. However it's hard to demonstrate the effectiveness of indexes on such small tables. Normally you wouldn't need an index for a table with less than one thousand records.
E.g. In my current job, I have a table with sales (one of many) in which I have at least eighteenth million headers per month. Which means that we have at least a total of 2 592 000 000 headers only. Imagine how many positions, products and payment records we have.
And to be honest you need to be extremely careful with indexes.
Because they not only increase storage, but degrade overall database performance while data grows.
One of the solutions is to move to Hadoop or Data Lake infrastructure or use horizontal scalable databases such as ScyllaDB or CockroachDB.
Thanks for the article. 🐬
PS: one thing that I saved in my brain, is that mysql can use only one index per table per query (I know there are some specials for joins and index merge)
It‘s a very good article. Showing benefits and shortcomings of the different types of indexes. Also the important note, that indexes come at a cost.
Thanks for writing.
Uday Kiran Kavaturu
IIIT-B | CovUni | Passionate AI ML Web Software Engineer | Solves problems through web apps and ML models
Is the indexing data structure same with NoSQL databases like Mongo db?