SQL behind the scenes
-
Let's say you have a SQL database with a table called USERS. Let's say it has 500,000 users in it. Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table. Does the database just do a linear search thru each user? Thank you.
-
Let's say you have a SQL database with a table called USERS. Let's say it has 500,000 users in it. Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table. Does the database just do a linear search thru each user? Thank you.
-
Let's say you have a SQL database with a table called USERS. Let's say it has 500,000 users in it. Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table. Does the database just do a linear search thru each user? Thank you.
Yes. It will scan segments and it breaks once all predicates are met. That's why having a PK with a clustered index is so very nice to have. Who comes up with the idea of a non-indexed table? :|
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Let's say you have a SQL database with a table called USERS. Let's say it has 500,000 users in it. Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table. Does the database just do a linear search thru each user? Thank you.
In data base talk it is known as a 'table scan'. Most databases have a way which allows you to see what it is doing. Oracle, SQL Server and MySQL IDEs all have a way to see this clearly. The nomenclature used in the output is hard to read but practice makes that easier.
-
Let's say you have a SQL database with a table called USERS. Let's say it has 500,000 users in it. Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table. Does the database just do a linear search thru each user? Thank you.
-
Let's say you have a SQL database with a table called USERS. Let's say it has 500,000 users in it. Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table. Does the database just do a linear search thru each user? Thank you.
See the question above; you have an index with two columns. That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2. So, yes, you search those linear. For a table without index, that means scanning the entire table as there is no guaranteed order to the rows.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
See the question above; you have an index with two columns. That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2. So, yes, you search those linear. For a table without index, that means scanning the entire table as there is no guaranteed order to the rows.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
"index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2." (My quote button still not working.) Re-read that response after looking at post to other response. That phrasing is a misstatement about how it works. A table storage in a database is quite complicated. Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster. There are some implementation details about how that works technically but 'at the end' is the best technical description. Clustered indexes do this differently but not specifically in terms of sorting anything. After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself. Sorting is not something that is related to the table nor even to indexes. In SQL you can specifically ask for a specific sort order. If you do not do that then how the records (rows) are returned is somewhat random. And that becomes even more true when things like joins are factored in.
-
"index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2." (My quote button still not working.) Re-read that response after looking at post to other response. That phrasing is a misstatement about how it works. A table storage in a database is quite complicated. Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster. There are some implementation details about how that works technically but 'at the end' is the best technical description. Clustered indexes do this differently but not specifically in terms of sorting anything. After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself. Sorting is not something that is related to the table nor even to indexes. In SQL you can specifically ask for a specific sort order. If you do not do that then how the records (rows) are returned is somewhat random. And that becomes even more true when things like joins are factored in.
jschell wrote:
That phrasing is a misstatement about how it works.
Please, explain. Please do? Pretty please? Do explain how it works?
jschell wrote:
A table storage in a database is quite complicated
Nope, and quite well documented.
jschell wrote:
Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster.
Tables are without any inherent order, by design and definition. Indexes aren't in the table, they are entities outside the table that may be updated.
jschell wrote:
After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself.
Hence, the "create index" expects some columns to sort on.
jschell wrote:
Sorting is not something that is related to the table nor even to indexes
If you need not sort, you need no index. So yes, it might be a tad related. The definition in proper schooling talks more about lookups than sorting, as that is more common.
jschell wrote:
In SQL you can specifically ask for a specific sort order
Thanks for explaining that, that's really something new.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.