The SELECT Command
The SELECT command is used to query the database. There are a number of points to remember when intending to build efficient queries:
SELECT NAME FROM AUTHOR;
This is faster than this query:
SELECT * FROM AUTHOR;If there is an index, use it. Reading field values directly from an index without reading a table at all is faster because the index occupies less physical space. There is, therefore, less I/O activity. In the ERD snippet shown in Figure 8-1, reading the EDITION table, with the following query should be able to force a direct read of the index because primary keys are automatically indexed. The ISBN field is the primary key for the EDITION table.
SELECT ISBN FROM EDITION;
Figure 8-1: Reading indexes instead of tables.
Not all database engines allow direct index scans, even when a SELECT command might encourage it.
Simple aliases: Shorter alias names can help to keep SQL code more easily readable, particularly for programmers in the future having to make changes. Maintainable code is less prone to error and much easier to tune properly. Consider the following query:
SELECT A.NAME, P.TITLE, E.ISBN
FROM AUTHOR A JOIN PUBLICATION P USING (AUTHOR_ID)
JOIN EDITION E USING (PUBLICATION_ID);
This is much easier to deal with than this query:
SELECT AUTHOR.NAME, PUBLICATION.TITLE, EDITION.ISBN
FROM AUTHOR JOIN PUBLICATION USING (AUTHOR_ID)
JOIN EDITION USING (PUBLICATION_ID);
Why? There is less code. Less code is easier to handle. Less is more in this case.
The above tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.
This was first published in April 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation