Deprecated: add_custom_background is deprecated since version 3.4.0! Use add_theme_support( 'custom-background', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Deprecated: add_custom_image_header is deprecated since version 3.4.0! Use add_theme_support( 'custom-header', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the jquery handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the smoothscroll handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Deprecated: The called constructor method for WP_Widget in Yoko_SocialLinks_Widget is deprecated since version 4.3.0! Use __construct() instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5177
Intro to Databases: Indexes | Megan Taylor

Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Indexes

also Indices

Primary mechanism to get improved performance on a database
Persistent data structure, stored in database

Functionality
Index on T.A or T.B or T.(A,B) allows you to ask a question based on that index instead of scanning the whole table.

Utility
Index = difference between full table scans and immediate location of tuples.
Orders of maginitude performance difference

Underlying data structures
– Balanced trees (B trees, B+ trees) A = V, A < V logarithmic in running time - Hash tables A = V constant running time Select sName From Student Where sID = 18942 Index on sID Many DBMS's build indexes automatically on PRIMARY KEY and sometimes UNIQUE attributes. Select sID From Student Where sName = 'Mary' and GPA > 3.9
Index on sName
Index on GPA
Index on (sName, GPA)

Downsides of Indexes
1) take up extra space
2) Index creation
3) Index maintenance

Picking which indexes to create:
size of table and possibly layout
data distributions
query vs update load

Physical design advisors
Input: database (statistics) and workload
Output: recommended indexes

Query Optimizer: looks at db stats. query or update, indexes and finds the best execution plan with estimated cost

SQL Syntax
Create Index IndexName on T(A)
Create Index IndexName on T(A1, A2, …, An)
Create Unique Index IndexName on T(A)
Drop Index IndexName

November 18, 2011 | Comments Off on Intro to Databases: Indexes | Categories: Posts | Permalink

Comments are closed.


Notice: Undefined index: host in /home/pqmz7qzy9yt5/public_html/wp-content/plugins/jetpack/modules/stats.php on line 209