1 / 14

Database Indexing

After this lecture, you should be able to: Understand why we need database indexing . Define indexes for your tables in MySQL. See the performance improvement of Indexing over No-Indexing . Database Indexing. Employee ( EID (auto-increment), Age, Salary)

ciqala
Download Presentation

Database Indexing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. After this lecture, you should be able to: Understand why we need database indexing. Define indexes for your tables in MySQL. See the performance improvement of Indexing over No-Indexing. Database Indexing

  2. Employee (EID(auto-increment), Age, Salary) Easy to retrieve employees by EID in increasing (or decreasing) order. Difficult to retrieve all employees who are 55 years old (age) -> scan the entire table file. One Example… Solution: Create an index on age field -> the same idea with book index.

  3. Database indexing is a technique to help efficiently access a collection of records in multiple ways. A database index is a data structure that improves the speed of operations on a database table. Indexes can be created using one or more columns of a database table. Note: in MySQL, a primary key column is automatically indexed for efficiency. Database Indexing Overview

  4. Table is stored in a file. File of records is a collection of records that may reside on several pages. Record id (rid) is sufficient to physically locate record. Pages (Data Blocks) (4KB or 8KB) are stored on disk. Indexes are data structures that allow us to find the record ids of records with given values in index search key fields. Data on External Storage

  5. Indexes: Data structures to organize records via trees or hashing. Like sorted files, they speed up searches for a subset of records, based on values in certain (“search key”) fields Updates are much faster than in sorted files. Examples of indexing techniques: ordered files, B+ trees, hash based structures. Typically, index contains auxiliary information (access method) that directs searches to the desired data entries. Indexes

  6. Index Structure Examples Index on the primary key field

  7. Index Structure Examples A search tree

  8. Employee (EID, Age, Salary) Difficult to retrieve all employees who are 55 years old (age) -> scan the entire table file. Solution: Create an index on age field -> the same idea with book index. Defining Index in MySQL create index emp_age_idx using btree on Employee(Age); drop index emp_age_idx on Employee;

  9. 8 tabless_100_none s_100_btrees_1000_none s_1000_btrees_10000_none s_10000_btrees_100000_none s_100000_btree Table s_10000_btree(sno,sname,status,city): contains 10000 records and have a B-tree index on sname field. An experiment with database index create index s_10000_btree_sname_idx using btree on s_10000_btree(sname);

  10. Populating data $chars = "abcdefghijkmnopqrstuvwxyz023456789"; for ($i = 0; $i < 10000; $i++) { $sname = substr(str_shuffle($chars), 0, 15); $status = rand(1, 2000); $city = substr(str_shuffle($chars), 0, 10); $query = "Insert into s_10000_btree (sname, status, city) values ('$sname', $status, '$city')"; $result = mysql_query($query); echo "Executing: $query - result: $result</br>"; }

  11. Random Searches - Queries select SQL_NO_CACHE * from s_100_none where sname = 'abcdefghijkmnop‘; … select SQL_NO_CACHE * from s_100000_none where sname = 'abcdefghijkmnop‘; /* */ select SQL_NO_CACHE * from s_100_btree where sname = 'abcdefghijkmnop' select SQL_NO_CACHE * from s_100000_btree where sname = 'abcdefghijkmnop’;

  12. Random Searches - Result

  13. Sequential Searches - Queries … select SQL_NO_CACHE max(sname) from s_100000_none where sname > 'abcdefghijkmnop' and sname < 'rmo8s5jh4ciyxfe'; /* */ … select SQL_NO_CACHE max(sname) from s_100000_btree where sname > 'abcdefghijkmnop' and sname < 'rmo8s5jh4ciyxfe';

  14. Sequential Searches - Result

More Related