> > So here, indexing comes into picture. What indexing does is, sets up the column your search conditions are on, in a sorted order to assist in optimising query performance.
> That's mysql specific, and not just that but it's innodb specific: innodb uses the primary key as a clustering index, or the first UNIQUE index if there's no PK.
> Other engines don't usually do that, some (e.g. postgres) don't even have clustered indexes (aka indexes which affect the table's own layout), in postgres clustering is an explicit discrete table-rewriting operation.
It kind of depends on exactly what they mean. The physical index itself is essentially always going to be stored in a "sorted" order, clustered or non-clustered. It's [by default almost always] a B-tree of some flavor, but every index is going to be stored in a way that makes searching it faster, and in that sense it can always be described as "sorted" even if the table itself has no clustering index. It's in a well-defined order that aids in identifying the records in the table itself. Even if the query engine identifies that the 100 records that satisfy the WHERE clause are in 100 different, discontinuous pages, an index can help with that even if the I/O won't be optimal.
> > The major advantage of B-tree is that the data in it is sortable
> That's… not really true. A b-tree is sorted, period. You can't have an unordered btree.
Oh, I've just noticed you know this already. What's your confusion then?
> It kind of depends on exactly what they mean. The physical index itself is essentially always going to be stored in a "sorted" order, clustered or non-clustered.
They're not "meaning" anything, they're literally saying that adding an index sorts the table by the indexed column, with a little picture showing this change to the table itself.
> Oh, I've just noticed you know this already. What's your confusion then?
I'm not confused, I'm pointing out that the article is factually incorrect. Not just "this only applies to one database" but literally saying something which is not true, or at best hopelessly confused.
> They're not "meaning" anything, they're literally saying that adding an index sorts the table by the indexed column, with a little picture showing this change to the table itself.
No, I don't think so. That's one interpretation of what they said. Since that's clearly not correct, why should we assume that's what it means? That's dishonest reading.
If I'm explaining to a beginner how an index works -- which is literally what this article is doing -- then I don't see why I wouldn't refer to an index as a specially sorted version of the records in the table. That's not a huge difference from what the author actually wrote. Yeah, you might prefer that the author had said "organized" rather than "sorted", but those two words are synonyms in plain English.
Look, here's the statement in context:
> What indexing does is, sets up the column your search conditions are on, in a sorted order to assist in optimising query performance.
> With index on company_id, table would look like this <image>
> Now, due to sorted order, the database knows which records to examine and when to stop.
> The main point of having a index is to cut down the number of records/rows in a table which needs to be examined by the database, to speed up the searching queries.
If you look at an index physically, it does literally store the values being indexed. That's why indexes take up disk space and require I/O. They're actually duplicating information. It does actually take the column values from the table and "sort" or "organize" the columns covered by the index. They're organized into a B-tree [usually], but the values from the table for the indexed columns are there and they are organized in a way you can describe as "sorted." The big difference is that the pages of the index, rather than having leaf records like a table, instead reference the records directly (often using an identifier tied to the clustered unique index).
> That's mysql specific, and not just that but it's innodb specific: innodb uses the primary key as a clustering index, or the first UNIQUE index if there's no PK.
> Other engines don't usually do that, some (e.g. postgres) don't even have clustered indexes (aka indexes which affect the table's own layout), in postgres clustering is an explicit discrete table-rewriting operation.
It kind of depends on exactly what they mean. The physical index itself is essentially always going to be stored in a "sorted" order, clustered or non-clustered. It's [by default almost always] a B-tree of some flavor, but every index is going to be stored in a way that makes searching it faster, and in that sense it can always be described as "sorted" even if the table itself has no clustering index. It's in a well-defined order that aids in identifying the records in the table itself. Even if the query engine identifies that the 100 records that satisfy the WHERE clause are in 100 different, discontinuous pages, an index can help with that even if the I/O won't be optimal.
> > The major advantage of B-tree is that the data in it is sortable
> That's… not really true. A b-tree is sorted, period. You can't have an unordered btree.
Oh, I've just noticed you know this already. What's your confusion then?