How SQL Indexes Work internally ?


When an index is created, it records the location of values in a table that are associated with the column that is indexed. Entries are added to the index when new data is added to the table. When a query is executed against the database and a condition is specified on a column in the WHERE clause that is indexed, the index is first searched for the values specified in the WHERE clause. If the value is found in the index, the index returns the exact location of the searched data in the table. Figure 16.1 illustrates how an index functions.




Suppose the following query was issued:

SELECT * 
FROM TABLE_NAME
WHERE NAME = 'SMITH';

As shown in Figure 16.1, the NAME index is referenced to resolve the location of all names equal to 'SMITH'. After the location is determined, the data can be quickly retrieved from the table. The data, in this case names, is alphabetized in the index.

A full table scan would occur if there were no index on the table and the same query was executed, which means that every row of data in the table would be read to retrieve information pertaining to all individuals with the name SMITH.

Comments

Post a Comment

Popular posts from this blog

UML,HLD and LLD.

UML is a standard language for specifying, visualizing, constructing, and documenting the artifacts of software systems. Here is a Complete understanding of UML.   http://www.tutorialspoint.com/uml/index.html High Level Design (HLD) is the overall system design - covering the system architecture and database design. It describes the relation between various modules and functions of the system. data flow, flow charts and data structures are covered under HLD. Low Level Design (LLD) is like detailing the HLD. It defines the actual logic for each and every component of the system. Class diagrams with all the methods and relation between classes comes under LLD. Programs specs are covered under LLD. In HLD, you design the component level abstractions and interactions. In LLD, for each component, you go to the lower level such as class and method levels, describe what each class's responsibility, its interface and its interaction with other classes. All terms like "high
Read more

XML Schema (XSD)

When beginning a study of XML, some may encounter an unfamiliar term that leaves them asking the question, “What is an XML schema (XSD)?” An XML schema is a road map for the XML document similar to a Document Type Definition (DTD). Created by the World Wide Web Consortium (W3C), schemas describe the elements and map out the presentation and nesting of XML documents. Essentially, the schema enables all applications to understand the flow of the page and validate the elements. Why Use An XML Schema? Step away from websites for a minute and consider house building. If the plumber, electrician and contractor all do their own thing, the result is a building with an uneven line, sinks in the bedroom and cable hookups in the bathroom. That does not happen because the architect draws a diagram that maps out the basic structure. With a blueprint, there is no guesswork. The plumber understands where the sinks go, and the electrician knows what rooms need what type of wiring. A schema is the
Read more