![]() Anyone can have that first name, regardless of their last name,so you have to search through the entire book line by line.The telephone book is ordered by last name and then by first name,just like a compound database index on last_name, first_name. All the people with the same last name arelisted together, because that’s how the telephone book is ordered.However, if I ask you to look up everyone in the telephone book whosefirst name is Charles, this doesn’t benefit from the order of names in thebook. If you cover a database table with indexes, you incur a lot ofoverhead with no assurance of payoff.When No Index Can HelpThe next type of mistake is to run a query that can’t use any index.Developers create more and more indexes, trying to find some magicalcombination of columns or index options to make their query run faster.We can think of a database index using an analogy to a telephone book.If I ask you to look up everyone in the telephone book whose last nameis Charles, it’s an easy task. and it fell on the floor.Report erratumthis copy is (P1.0 printing, May 2010)Please purchase PDF Split-Merge on to remove this watermark.ANTIPATTERN: USING INDEXES WITHOUT A PLAN 152Some people create indexes on every column-and every combinationof columns-because they don’t know which indexes will benefit theirqueries. So he bought $200 in quarter chips,went to the roulette table, and put chips on every square, red and black.He covered the table. Also, the order of columns in acompound index is important you should use the columns left-to-right in search criteria, join criteria, or sorting order.Hedging Your BetsBill Cosby told a story about his vacation in Las Vegas: He was sofrustrated by losing in the casinos that he decided he had to winsomething-once-before he left. Also, you’renot likely to run queries that search or sort by the full summarycolumn.➌ hours: This is another example of a column that you’r e probably notgoing to search for specific values.➍ bug_id, date_reported, status: There are good reasons to use com-pound indexes, but many people create compound indexes thatare redundant or seldom used. Youneed to read the documentation for the database you use.➋ summary: An indexing for a long string datatype like VARCHAR(80) islarger than an index for a more compact data type. Each database brandhas its own rules for when to create an index automatically. There’s no benefitto it, and it could just be extra overhead. Here are someexamples:Download Index-Shotgun/anti/create-table.sqlCREATE TABLE Bugs (bug_id SERIAL PRIMARY KEY,date_reported DATE NOT NULL,summary VARCHAR(80) NOT NULL,status VARCHAR(10) NOT NULL,hours NUMERIC(9,2),➊INDEX (bug_id),➋INDEX (summary),➌INDEX (hours),➍INDEX (bug_id, date_reported, status)) In the previous example, there are several useless indexes:➊ bug_id: Most databases create an index automatically for a primarykey, so it’s redundant to define another index. ANTIPATTERN: USING INDEXES WITHOUT A PLAN 151Too Many IndexesYou benefit from an index only if you run queries that use that index.There’s no benefit to creating indexes that you don’t use.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |