[Solved] SQlite query slow just add a \"and\" judgment

EverSQL Database Performance Knowledge Base

SQlite query slow just add a \"and\" judgment

SELECT distinct
    *
    FROM

    dictionary d  
    join dictionary_with_word dw on dw.dictionary_id=d.dictionary_id  
    join word w on w.word_id=dw.word_id  
    join word_day wd on  wd.word_id=w.word_id  
    join have_buy_dictionary hbd on hbd.have_buy_dictionary_dictionary=d.dictionary_id

    where wd.user_id=777 and wd.word_day=0

I use above sql query was fast.

But when i add sql code "and d.dictionary_id=9" At the end of above sql statement :

SELECT distinct
    *
    FROM

    dictionary d  
    join dictionary_with_word dw on dw.dictionary_id=d.dictionary_id  
    join word w on w.word_id=dw.word_id  
    join word_day wd on  wd.word_id=w.word_id  
    join have_buy_dictionary hbd on hbd.have_buy_dictionary_dictionary=d.dictionary_id

    where wd.user_id=777 and wd.word_day=0 
    and d.dictionary_id=9

Then will be very slow!

why ?

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. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. 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 `dictionary` ADD INDEX `dictionary_idx_dictionary_id` (`dictionary_id`);
ALTER TABLE `dictionary_with_word` ADD INDEX `dictionary_word_idx_word_id` (`word_id`);
ALTER TABLE `have_buy_dictionary` ADD INDEX `have_dictionary_idx_have_dictionary` (`have_buy_dictionary_dictionary`);
ALTER TABLE `word` ADD INDEX `word_idx_word_id` (`word_id`);
ALTER TABLE `word_day` ADD INDEX `word_day_idx_user_id_word_day` (`user_id`,`word_day`);
The optimized query:
SELECT
        DISTINCT * 
    FROM
        dictionary d 
    JOIN
        dictionary_with_word dw 
            ON dw.dictionary_id = d.dictionary_id 
    JOIN
        word w 
            ON w.word_id = dw.word_id 
    JOIN
        word_day wd 
            ON wd.word_id = w.word_id 
    JOIN
        have_buy_dictionary hbd 
            ON hbd.have_buy_dictionary_dictionary = d.dictionary_id 
    WHERE
        wd.user_id = 777 
        AND wd.word_day = 0

Related Articles



* original question posted on StackOverflow here.