[Solved] SQL Separating big fields for speeding up queries
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

SQL Separating big fields for speeding up queries

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)

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `BOOK` ADD INDEX `book_idx_file_extension` (`FILE_EXTENSION`);
The optimized query:
SELECT
        BOOK.BOOK_ID 
    FROM
        BOOK 
    WHERE
        BOOK.FILE_EXTENSION = 'PDF'

Related Articles



* original question posted on StackOverflow here.