![]() The index will actually look like this: company_id ![]() Basically the index holds the company_id and that particular row’s home address on the memory disk. How Does the Database Know What Other Fields in the Table to Return?ĭatabase indexes will also store pointers which are simply reference information for the location of the additional information in memory. Units and unit_cost will not be held in the data structure. Our data structure for the table above will only contain the the company_id numbers. When the index creates a data structure on a specific column it is important to note that no other column is stored in the data structure. When the data structure is sorted in order it makes our search more efficient for the obvious reasons we pointed out above. While the advantages of the B-Tree are numerous, the main advantage for our purposes is that it is sortable. ![]() The data structure type is very likely a B-Tree. In actuality, what happens is the index causes the database to create a data structure. In reality the database table does not reorder itself every time the query conditions change in order to optimize the query performance: that would be unrealistic. If the next row’s company_id is 20, the query knows to stop searching and the query will finish. If the next row’s comapny_id number is also 18 then it will return the all the columns requested in the query. Now, the database can search for company_id number 18 and return all the requested columns for that row then move on to the next row. With an index on the company_id column, the table would, essentially, “look” like this: company_id What indexing does is sets up the column you’re search conditions are on in a sorted order to assist in optimizing query performance. Querying an unindexed table, if presented visually, would look like this: Tables increase in size and searching increases in execution time. You can see how this becomes problematic in our ever data saturated world. As the sophistication of the data increases, what could eventually happen is that a table with one billion rows is joined with another table with one billion rows the query now has to search through twice the amount of rows costing twice the amount of time. This will only get more and more time consuming as the size of the table increases. So to search for all of the potential instances of the company_id number 18, the database must look through the entire table for all appearances of 18 in the company_id column. The database would have to search through all 17 rows in the order they appear in the table, from top to bottom, one at a time.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |