Hi guys, I've got a big problem about select performance on an left join. I have two tables:
CREATE TABLE Song ( id INTEGER NOT NULL UNIQUE, title VARCHAR(40) NULL COLLATE NOCASE, artist VARCHAR(40) NULL COLLATE NOCASE, bpm INT NULL, genre_id INT NULL, PRIMARY KEY (id), CONSTRAINT fk_Genre FOREIGN KEY (genre_id) REFERENCES Song (id) ON DELETE SET NULL ON UPDATE CASCADE); -- Indeces CREATE INDEX Song_title_idx ON Song(title); CREATE INDEX Song_artist_idx ON Song(artist); CREATE INDEX Song_bpm_idx ON Song(bpm); CREATE INDEX Song_genre_idx ON Song(genre_id); CREATE TABLE Genre ( id INTEGER NOT NULL UNIQUE, name VARCHAR(20) NOT NULL COLLATE NOCASE, image_id INT NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_Image2 FOREIGN KEY (image_id) REFERENCES Genre (id) ON DELETE SET NULL ON UPDATE CASCADE); -- Indeces CREATE INDEX Genre_name_idx ON Genre(name); - Now when I execute this query: SELECT S.id,title,artist,bpm,name FROM Song AS S LEFT JOIN Genre AS G ON (S.genre_id = G.id) WHERE title<= 'zzX_5238' AND (title< 'zzX_5238' OR S.id< 5238) ORDER BY title DESC, S.id DESC LIMIT 20; it takes only 200 ms but when I execute: SELECT S.id,title,artist,bpm,name FROM Song AS S LEFT JOIN Genre AS G ON (S.genre_id = G.id) WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122) ORDER BY name DESC, S.id DESC LIMIT 20; it takes 8100! It's a huge time for our application! I also noticed that the Genre_name_idx it's useless. Song has 10000 records, Genre has 100 records. Does anyone have any ideas about how to improve the previous query? Thanks _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
