Let's say I have a table BOOK:
BOOK_ID INT(6) PK -------------------- FILE_EXTENSION VARCHAR(5) TITLE VARCHAR(60) LANGUAGE VARCHAR(10) EDITION INT(2) PUBLISHMENT_OFFICE_ID INT(4) PUBLISH_YEAR INT(4) RATING INT(1) FILE_UPDOAD_DATE DATE LINK VARCHAR(150)
This table is meant to be used both for searching books (for ex. by extension, by publishment office, by authors (from other tables), etc) and for full visualization (print on page all books with all these fields).
So there is a question: For example, if I do
SELECT BOOK_ID FROM BOOK WHERE FILE_EXTENSION = 'PDF'
will this cause the load of all big fields (link, title, and maybe planned BLOB) as an intermediate result, or will it discard any unnecessary fields as soon as WHERE clause is translated with no performance issues?
The question leads for solution: separate big fields in other table with same PK in order to slow down visualization (cuz a JOIN is needed) but to speed up the search? Is it worth?
P.S. This particular DB is not meant to hold rly big amount of data, so my queries (I hope) won't be as slow. But this question is about general databases' design (let's say 10^8 entries).
P.P.S. Pls don't link me to database normalization (my full DB is normilized well)
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `BOOK` ADD INDEX `book_idx_file_extension` (`FILE_EXTENSION`);
SELECT BOOK.BOOK_ID FROM BOOK WHERE BOOK.FILE_EXTENSION = 'PDF'