Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 9:45 PM, Yuzem wrote: > It is incredibly fast but it gives me the wrong result: Yeah… you forgot the 'where' clause in the subquery… you need to restrict it on the genre ___ sqlite-users mailing list

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Thanks Petite Abeille, I translated your code to this: SELECT genres.genres, ( SELECT substr(group_concat(name,' '),1,60) FROM ( SELECTname FROM movies JOIN genres ON

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 11:27 PM, Yuzem wrote: > Any clue on why LEFT JOIN is so slow when used with genres but not with > larger tables? Sure. But your conclusion is a most likely a red herring. The crux of the matter is that inner and outer join have a wildly different

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Petite Abeille-2 wrote > Sure. But your conclusion is a most likely a red herring. The crux of the > matter is that inner and outer join have a wildly different semantic. And > therefore execution plan. That's all. Seems all very reasonable from an > optimizer point of view. But I have no

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 10:28 PM, Yuzem wrote: > Ok, wonderful, now it is working correctly but how do I select multiple > columns from table movies? > Should I add another sub query? Nope. You have now changed the problem definition, so scalars will not be a good fit.

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Thanks but 4 rows is not what I am looking for. I found a solution, concatenation: SELECT genres.genres, ( SELECT group_concat(movie,' ') FROM ( SELECTmovies.movies||','||name movie FROM movies JOIN

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Ok, wonderful, now it is working correctly but how do I select multiple columns from table movies? Should I add another sub query? Example: SELECT genres.genres, ( SELECT substr(group_concat(name,' '),1,60) FROM ( SELECTname

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 8:56 PM, Yuzem wrote: > SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies > GROUP BY genres ORDER BY genres; > time: 2.475s > > SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP > BY genres ORDER BY

Re: [sqlite] Please help me optimize this LEFT JOIN query.

I did some testing and found some strange results. SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 2.475s SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 0.035s

Re: [sqlite] Please help me optimize this LEFT JOIN query.

I did an ANALYZE but I'm getting the same results. I do have indexes: CREATE TABLE movies ( movies UNIQUE, name, icon_modified ); CREATE TABLE genres ( genres, movies, UNIQUE(genres,movies) ); people has an index on people (people UNIQUE) and tasks is a view: CREATE VIEW tasks AS

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On 5 Sep 2013, at 7:56pm, Yuzem wrote: > I did some testing and found some strange results. Please do an ANALYZE and try the same things again. Also, do you have any indexes on those tables (apart from the primary keys, of course) ? Simon.

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 4, 2013, at 4:21 PM, Yuzem wrote: > I want to construct genres icons and each icon must display 4 movies. Assuming this is IMDB… what about a scalar subquery? For example, assuming a slightly different schema from yours: selectgenre.code as genre, (

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Thanks Igor but now it is taking even more time: 3.139s Any other idea? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71003.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On 9/4/2013 10:21 AM, Yuzem wrote: SELECT genres name,count(genres.movies) count,substr(group_concat(grouped.movies,' '),1,40) src,substr(group_concat(grouped.icon_modified,' '),1,80) icon_modified FROM genres LEFT JOIN (SELECT movies,icon_modified FROM movies WHERE icon_modified != '') grouped

[sqlite] Please help me optimize this LEFT JOIN query.

I have 2 tables: CREATE TABLE movies ( movies UNIQUE, name, icon_modified ); CREATE TABLE genres ( genres, movies, ); I want to construct genres icons and each icon must display 4 movies. I need to get: genres from genres and movies,icon_modified from movies. This is the query I have: