Wednesday, June 5, 2019

Advantages And Disadvantages To Using Indexes Computer Science Essay

Advantages And Disadvantages To Using Indexes Computer Science EssayPut simply, database indexes do speed up retrieval of data. The other great benefit of indexes is that your innkeeper doesnt film to work as voiceless to get the data. They are a great deal the same as book indexes, providing the database with quick jump points on where to find the sound reference (or to find the database row).There are both advantages and disadvantages to using indexes,however.One disadvantage is they can take up quite a bit of post check a textbook or reference guide and youll see it takes quite a few pages to take on those page references.Another disadvantage is using too many indexes can actually slow your database d bear. Thinking of a book again, imagine if e precise the, and or at was included in the index. That would recrudesce the index being uptakeful the index becomes as big as the text On top of that, each time a page or database row is updated or removed, the reference or in dex also has to be updated.So indexes speed up finding data, but slow down inserting, modify or deleting data.Some fields are automatically indexed. A primary key or a field marked as unique for lesson an email address, a userid or a social security number are automatically indexed so the database can quickly check to make sealed that youre not going to introduce bad data.So when should a database field be indexed?The general rule is anything that is employ to limit the number of results youre trying to find.Its hard to generalise so well look at any(prenominal) specific but common cases.Note the database control boards shown downstairs are used as an example only and lead not necessarily be the best setup for your particular films.In a database delay that looks exchangeable thisNote The SQL code shown below works with both MySQL and PostgreSQL databases. perform TABLE subscribers (subscriberid INT PRIMARY KEY,emailaddress VARCHAR(255),firstname VARCHAR(255),lastname VARCHAR(255))if we want to quickly find an email address, we create an index on the emailaddress field give rise major power subscriber_email ON subscribers(emailaddress) and any time we want to find an email addressSELECT firstname, lastname FROM subscribers WHERE emailaddress=emailprotected it will be quite quick to findAnother spring for creating indexes is for tables that reference other tables. For example, in a CMS you might drive a news table that looks something like thisCREATE TABLE newsitem (newsid INT PRIMARY KEY,newstitle VARCHAR(255),news centre TEXT,authorid INT,newsdate TIMESTAMP)and another table for authorsCREATE TABLE authors (authorid INT PRIMARY KEY,username VARCHAR(255),firstname VARCHAR(255),lastname VARCHAR(255))A interrogative like thisSELECT newstitle, firstname, lastname FROM newsitem n, authors a WHERE n.authorid=a.authorid will be take advantage of an index on the newsitem authoridCREATE indicator newsitem_authorid ON newsitem(authorid)This allows th e database to very quickly get together the records from the newsitem table to the authors table. In database terminology this is called a table join you should index any fields abstruse in a table join like this.Since the authorid in the authors table is a primary key, it is already indexed. The same goes for the newsid in the news table, so we dont need to look at those cases.On a side note, table aliases make things a lot easier to see whats happening. Using newsitem n and authors a means we dont have to writeSELECT newstitle, firstname, lastname FROM newsitem, authors WHERE newsitem.authorid=authors.authoridfor more complicated queries where more tables are referenced this can be extremely helpful and make things really easy to follow.In a more complicated example, a news item could exist in multiple categories, so in a design like thisCREATE TABLE newsitem (newsid INT PRIMARY KEY,newstitle VARCHAR(255),newscontent TEXT,authorid INT,newsdate TIMESTAMP)CREATE TABLE newsitem_ca tegories (newsid INT,categoryid INT)CREATE TABLE categories (categoryid INT PRIMARY KEY,categoryname VARCHAR(255))This querySELECT n.newstitle, c.categoryname FROM categories c, newsitem_categories nc, newsitem n WHERE c.categoryid=nc.categoryid AND nc.newsid=n.newsid will show all category name and newstitles for each category.To make this particular query fast we need to check we have an index onnewsitem newsidnewsitem_categories newsidnewsitem_categories categoryidcategories categoryidNote Because the newsitem newsid and the categories categoryid fields are primary keys, they already have indexes.We need to check there are indexes on the join table newsitem_categoriesThis will do itCREATE INDEX newscat_news ON newsitem_categories(newsid)CREATE INDEX newscat_cats ON newsitem_categories(categoryid)We could create an index like thisCREATE INDEX news_cats ON newsitem_categories(newsid, categoryid)However, doing this limits some ways the index can be used. A query against the table that uses both newsid and categoryid will be able to use this index. A query against the table that only gets the newsid will be able to use the index.A query against that table that only gets the categoryid will not be able to use the index.For a table like thisCREATE TABLE example (a int,b int,c int)With this indexCREATE INDEX example_index ON example(a,b,c)It will be used when you check against a.It will be used when you check against a and b.It will be used when you check against a, b and c.It will not be used if you check against b and c, or if you only check b or you only check c.It will be used when you check against a and c but only for the a column it wont be used to check the c column as well.A query against a OR b like thisSELECT a,b,c FROM example where a=1 OR b=2Will only be able to use the index to check the a column as well it wont be able to use it to check the b column.Multi-column indexes have quite specific uses, so check their use carefully.Now that weve seen w hen we should use indexes, lets look at when we shouldnt use them. They can actually slow down your database (some databases may actually choose to ignore the index if theres no reason to use it).A table like thisCREATE TABLE news (newsid INT PRIMARY KEY,newstitle VARCHAR(255),newscontent TEXT,active CHAR(1),featured CHAR(1),newsdate TIMESTAMP) looks pretty standard. The active field tells us whether the news item is active and ready to be viewed on the site.So should we should create an index on this field for a query like this?SELECT newsid, newstitle FROM news WHERE active=1No, we shouldnt.If most of your content is live, this index will take up extra space and slow the query down because almost all of the fields match this criteria. Imagine cholecalciferol news items in the database with 495 being active. Its quicker to eliminate the ones that arent active than it is to list all of the active ones (if you do have an index on the active field, some databases will choose to ignor e it anyway because it will slow the query down).The featured field tells us whether the news item should feature on the preliminary page. Should we index this field? Yes. Most of our content is not featured, so an index on the featured column will be quite useful. another(prenominal) examples of when to index a field include if youre going to order by it in a query. To get the most recent news items, we do a query like thisSELECT newtitle, newscontent FROM news ORDER BY newsdate DESCCreating an index on newsdate will allow the database to quickly sort the results so it can bugger off the items in the right order. Indexing can be a bit tricky to get right, however there are tools available for each database to help you work out if its working as it should.Well there you have it my introduction to database indexes. Hopefully youve learned something from this article and can apply what youve learned to your own databases.This entry was posted in Programming. Bookmark the permalink. 22 Responses to Introduction to Database IndexesJim saysFebruary 17, 2006 at 713 amI think you need to be a bit more the reader knows absolutly nothing when describing the table joins. You lost me for a bit there. Perhaps a better step by step reach holding example would be better. Editors note Sure thing. Ill see what I can come up with for next month If youre desperate for study and cant wait drop me a line chris at interspire dot com and Ill explain it further Replykhani saysMay 14, 2006 at 355 pmGood effort chris,You ve described Indexes in a simple way.ReplyVRS saysMay 24, 2006 at 132 pmGood article.Do include some explanation on clustered and non clustered indexes.ReplyVivek saysJuly 13, 2006 at 325 amGood article. Helped a lot in understading the basic principle of index. ThanksReplyUnknown saysOctober 11, 2006 at 843 pmGood article man. I really appretiate your effort.ReplyAyaz saysNovember 14, 2006 at 922 amGood article to substantiate indexes for a beginner.ReplyDebi z saysNovember 27, 2006 at 521 pmVery well written and simply explained for those feeling for a basic overviewReplyNand saysDecember 14, 2006 at 1146 amGood article, felt like walking over the bridge on a gorge. Can u pl. explain drawbacks of using index also. Chris note The main drawback is that every insert, update or delete has to change the index as well. If you have a lot of indexes, that adds a lot of overhead to the operation. ReplyMyo saysDecember 19, 2006 at 1156 pmVery easy to understand and gives examples with differentsituations to demonstrate when and where we should use indexes and why.Thanks manReplyJohn Lowe saysMarch 14, 2007 at 257 amA quick a useful reminder to what idexes are all about, thanks.ReplyShravanti saysJune 26, 2007 at 311 amGood Introduction to Indexes. It would also be valuable to have information on how do indexes work on OLAP side of a Data Warehouse.ReplyHarsha saysAugust 13, 2007 at 1121 pm bit tutorial.. good workReplykrish saysSeptember 24, 20 07 at 244 amReally very nice explanationReplyAlagesan saysOctober 10, 2007 at 1133 pmThis is a great article to learn indexing for beginners I really appreciate your efforts and good will in explaining them in wrangling here.ThanksReplyHeather saysOctober 12, 2007 at 823 amThis was a great explanation of indexes for me I am self-taught when it comes to databases so the language in this tutorial was very easy for me to understand. Also, you used great examples to help explain your information. THANKSReplyJess Duckin saysOctober 28, 2007 at 458 amThe explaination on the usage of indexing is very helpfulReplyMayur saysOctober 29, 2007 at 156 pmThank you very much, a really informative tutorialfor me it was a 100% match to what I was looking for. ThanksReplysatish soni saysJanuary 11, 2008 at 717 am long article on indexes even oracle has not provided that much knowledge about indexesReplyShweta saysJanuary 11, 2008 at 425 pmGood. Just the overview i needed.ReplyHemant Jirange saysJan uary 17, 2008 at 339 amGreat articlethis is very simple to understand whole disadvantages about indexReplyramesh saysJanuary 18, 2008 at 226 amimpossible.even wikipedi couldnt match your tutorial on this topicthank uuuuuuuuuuuuuuu very muchReplyRavi saysSeptember 12, 2008 at 557 amthanks Chris, was an easy read for a database novice. I look forward to see the next chapterReplyLeave a ReplyName (required)Mail (will not be published) (required)WebsiteHome Email Marketing Shopping Cart knowledge Management Software Content Management Software Ecommerce Software Sell Products Online Our Guarantee Privacy Policy Copyright 1999-2010 Interspire Pty. Ltd. ACN 107 422 631

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.