How can I improve this query?

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

How can I improve this query?

Yuzem
Hello, I have the following tables:
CREATE TABLE movies (movies,name);
CREATE TABLE genres (movies,genres);

Every movie has many genres and every genre has many movies.
I want to list all genres but those who match a specified movie must be marked.

For example for movie "tt1637725":
SELECT distinct genres,movies from genres WHERE movies='tt1637725'
UNION ALL
SELECT distinct genres,'' FROM genres WHERE genres NOT IN (SELECT distinct genres FROM genres WHERE movies='tt1637725') group by genres order by genres;

Action|
Adult|
Adventure|
Animation|
Biography|
Comedy|tt1637725
Crime|
Documentary|
Drama|
Family|
Fantasy|tt1637725
Film-Noir|
History|
Horror|
Music|
Musical|
Mystery|
Romance|
Sci-Fi|
Short|
Sport|
Thriller|
War|
Western|

Is there any way to improve that code?
Thanks in advance!
Reply | Threaded
Open this post in threaded view
|

Re: How can I improve this query?

Igor Tandetnik-2
On 3/5/2013 4:41 PM, Yuzem wrote:

> Hello, I have the following tables:
> CREATE TABLE movies (movies,name);
> CREATE TABLE genres (movies,genres);
>
> Every movie has many genres and every genre has many movies.
> I want to list all genres but those who match a specified movie must be
> marked.
>
> For example for movie "tt1637725":
> SELECT distinct genres,movies from genres WHERE movies='tt1637725'
> UNION ALL
> SELECT distinct genres,'' FROM genres WHERE genres NOT IN (SELECT distinct
> genres FROM genres WHERE movies='tt1637725') group by genres order by
> genres;

Try this:

select genres, max(case movies when 'tt1637725' then movies else '' end)
from genres group by genres order by genres;

An index on genres(genres) would help.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How can I improve this query?

Yuzem
Thank you very much, that worked perfectly, I have one more question.
Given the same schema, I can list all genres with one movie by genre:
SELECT genres, movies FROM genres GROUP BY genres;
Action|[movie id]
Adventure|[movie id]
Animation|[movie id]
Biography|[movie id]
Comedy|[movie id]
Crime|[movie id]
Documentary|[movie id]
etc...

I want to get a specifed number of movies by genre, for example 3 movies by genres:
Action|[movie id]
Action|[movie id]
Action|[movie id]
Adventure|[movie id]
Adventure|[movie id]
Adventure|[movie id]
Animation|[movie id]
Animation|[movie id]
Animation|[movie id]
etc...

Is that possible?
Thanks again!
Reply | Threaded
Open this post in threaded view
|

Re: How can I improve this query?

Petite Abeille-2
In reply to this post by Yuzem

On Mar 5, 2013, at 10:41 PM, Yuzem <[hidden email]> wrote:

> Hello, I have the following tables:
> CREATE TABLE movies (movies,name);
> CREATE TABLE genres (movies,genres);

IMDB?

>
> Every movie has many genres and every genre has many movies.
> I want to list all genres but those who match a specified movie must be
> marked.

You might be better off normalizing your data a bit…

Say… movie, genre, movie_genre… so…

select    movie.title,
          genre.code
from      movie

join      movie_genre
on        movie_genre.movie_id = movie.id

join      genre
on        genre.id = movie_genre.genre_id

where     movie.title = 'Ted (2012)'

order by  movie.title,
          genre.code

title|code
Ted (2012)|Comedy
Ted (2012)|Fantasy

Ok, now, given a movie, you can get its genre.

And now you can easily show all genres and the matching movie:

select    genre.code,
          MovieGenre.title
from      genre

left join (
            select  movie.title,
                    movie_genre.genre_id
            from    movie

            join    movie_genre
            on      movie_genre.movie_id = movie.id

            where   movie.title = 'Ted (2012)'
          )
as        MovieGenre
on        MovieGenre.genre_id = genre.id

order by  genre.code


code|title

Action|
Adult|
Adventure|
Animation|
Biography|
Comedy|Ted (2012)
Crime|
Documentary|
Drama|
Experimental|
Family|
Fantasy|Ted (2012)
Film-Noir|
Game-Show|
History|
Horror|
Lifestyle|
Music|
Musical|
Mystery|
News|
Reality-TV|
Romance|
Sci-Fi|
Short|
Sport|
Talk-Show|
Thriller|
War|
Western|

Bottom line:  there are about 30 genres in IMDB, but about 1,495,677 movie → genre mappings.

Take a look at database normalization:

http://en.wikipedia.org/wiki/Database_normalization


"Normalize until it hurts, denormalize until it works" -- anonymous

But never the other way round :)


Random Ted quote:  Show us how, Flash!

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Bullzip ms Access to MySql

veneff

Hi,

I have been trying to use Bullzip's Access to MySql converter to generate a SQL file that I can import into SQLite.
I'm using SQLite Manager under FireFox to import the resultant SQL file.
First, has anyone successfully done this.  Is there a better way.

A couple of my problems involve indexes:
The way Bullzip indicates Primary keys is different that what I see specified in the SQLite syntax definition.  And because of that it seems as if SQLite won't accept the AUTOINCREMENT (actually, AUTO INCREMENT was AUTO INCREMENT) constraint.

If a field is declared a Primary Key, is an index automatically built base on that field?

Here is an example of the create statement being generated:
CREATE TABLE `Condition_Result_Type` (
  `ConditionType_ID` INTEGER NOT NULL AUTOINCREMENT,
  `ConditionType` VARCHAR(50),
  `Description` VARCHAR(50),
  PRIMARY KEY (`ConditionType_ID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

I can write a program to re-arange the Primary Key declaration to help correct this problem.

So, the bottom line is that I need to convert this create statement into one that SQLite will accept.

one more thing, is there a way to search through the archived forums?

Vance
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bullzip ms Access to MySql

Robert Hairgrove
On Wed, 2013-03-06 at 21:36 +0000, [hidden email] wrote:
> ENGINE=myisam DEFAULT CHARSET=utf8;

Try removing the bit in the above quote. This is MySQL-specific code.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bullzip ms Access to MySql

Kevin Benson
In reply to this post by veneff
On Wed, Mar 6, 2013 at 4:36 PM, <[hidden email]> wrote:

>
> Hi,
>
>

> *SNIP



>

one more thing, is there a way to search through the archived forums?
>
> Vance
>


Here's one way:

http://www.mail-archive.com/search?a=1&l=sqlite-users@...&haswords=&from=&notwords=&subject=&datewithin=1d&date=&order=datenewest&search=Search
--
   --
      --
         --Ô¿Ô--
        K e V i N
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How can I improve this query?

Igor Tandetnik-2
In reply to this post by Yuzem
On 3/6/2013 10:20 AM, Yuzem wrote:

> I want to get a specifed number of movies by genre, for example 3 movies by
> genres:
> Action|[movie id]
> Action|[movie id]
> Action|[movie id]
> Adventure|[movie id]
> Adventure|[movie id]
> Adventure|[movie id]
> Animation|[movie id]
> Animation|[movie id]
> Animation|[movie id]
> etc...

select genres, movies from genres g1 where movies in
   (select g2.movies from genres g2 where g1.genres = g2.genres order by
g2.movies limit 3)
order by genres;

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bullzip ms Access to MySql

veneff
In reply to this post by Robert Hairgrove
It is more than that.

I tried removing your suggestion, then removed AUTOINCREMENT. Then it complained about the INDEX clause.
I'm working on a program to rearrange things like moving the Primary key declaration to the field constraint clause, moving the index declarations to index creation statements (or just removing it if field is declared a Primary key) after the table creation statement.

I haven't dealt with indexes in SQLite in the past, so a couple of follow up questions:
To index a column (a non primary key), I assume I have to create another column using the Create index with a new column name.
When a query is made to the table do I need to reference the index column name or can I reference the original column and still invoke the index?
 In MySQL it seems that I can simple declare a column as an index and and use that column's name in querys.  No need to worry about an additional column name.

Vance


on Mar 06, 2013, Robert Hairgrove <[hidden email]> wrote:

>
>On Wed, 2013-03-06 at 21:36 +0000, [hidden email] wrote:
>> ENGINE=myisam DEFAULT CHARSET=utf8;
>
>Try removing the bit in the above quote. This is MySQL-specific code.
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bullzip ms Access to MySql

Simon Slavin-3

On 7 Mar 2013, at 2:51pm, [hidden email] wrote:

> I haven't dealt with indexes in SQLite in the past, so a couple of follow up questions:
> To index a column (a non primary key), I assume I have to create another column using the Create index with a new column name.

There is no need to create a new column.  You can have a table which has existed for years with columns a, b, c, d.  Years after that table was originally created you can create a new index on existing columns of it:

CREATE INDEX myTable_b_c ON myTable (b, c)

The table is untouched and has exactly the same content as before.  SQL just has a new faster way of searching it which may be useful for some statements.  There are no additional columns anywhere.  This is true for all SQL engines, and it not an answer especially about SQLite.

> When a query is made to the table do I need to reference the index column name or can I reference the original column and still invoke the index?

It is SQL's job to know what indexes are available and to guess the fastest way to do whatever command or query you are executing.  If a programmer ever needs to specify which index SQL should use to do something, something is wrong.  The intelligence it takes to have SQL figure out which indexes to use and how to use them, without any input from the programmer, is one of the things that makes SQL so good.

> In MySQL it seems that I can simple declare a column as an index and and use that column's name in querys.

You may be confused.  In SQL you can use any column name in queries whether it is indexed or not.  Having a convenient index may make a command faster, but it doesn't magically let you do anything that wasn't possible without it [1].  Again, this is true of all SQL engines, not just SQLite.

> No need to worry about an additional column name.

SQLite needs no additional columns.  I'm not sure where you have read about additional columns being needed for any SQL engine, but the idea is not one that SQL users use.  You may have read some very weird books or tutorials, or you may be trying to use some knowledge you have about some other DBMS when working with SQL, where it doesn't apply.

[1] certain exceptions for some obscure advanced features (e.g. foreign keys in SQLite) but you don't care about that

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bullzip ms Access to MySql

veneff
Simon,

Thanks for the detailed response!

Can I say:
Create INDEX c on myTable(c);

Vance

on Mar 07, 2013, Simon Slavin <[hidden email]> wrote:

>
>
>On 7 Mar 2013, at 2:51pm, [hidden email] wrote:
>
>> I haven't dealt with indexes in SQLite in the past, so a couple of follow up questions:
>> To index a column (a non primary key), I assume I have to create another column
>using the Create index with a new column name.
>
>There is no need to create a new column.  You can have a table which has existed for
>years with columns a, b, c, d.  Years after that table was originally created you
>can create a new index on existing columns of it:
>
>CREATE INDEX myTable_b_c ON myTable (b, c)
>
>The table is untouched and has exactly the same content as before.  SQL just has a
>new faster way of searching it which may be useful for some statements.  There are
>no additional columns anywhere.  This is true for all SQL engines, and it not an
>answer especially about SQLite.
>
>> When a query is made to the table do I need to reference the index column name or
>can I reference the original column and still invoke the index?
>
>It is SQL's job to know what indexes are available and to guess the fastest way to
>do whatever command or query you are executing.  If a programmer ever needs to specify
>which index SQL should use to do something, something is wrong.  The intelligence
>it takes to have SQL figure out which indexes to use and how to use them, without
>any input from the programmer, is one of the things that makes SQL so good.
>
>> In MySQL it seems that I can simple declare a column as an index and and use that
>column's name in querys.
>
>You may be confused.  In SQL you can use any column name in queries whether it is
>indexed or not.  Having a convenient index may make a command faster, but it doesn't
>magically let you do anything that wasn't possible without it [1].  Again, this is
>true of all SQL engines, not just SQLite.
>
>> No need to worry about an additional column name.
>
>SQLite needs no additional columns.  I'm not sure where you have read about additional
>columns being needed for any SQL engine, but the idea is not one that SQL users use.
> You may have read some very weird books or tutorials, or you may be trying to use
>some knowledge you have about some other DBMS when working with SQL, where it doesn't
>apply.
>
>[1] certain exceptions for some obscure advanced features (e.g. foreign keys in SQLite)
>but you don't care about that
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bullzip ms Access to MySql

Simon Slavin-3

On 7 Mar 2013, at 5:51pm, [hidden email] wrote:

> Simon,
>
> Thanks for the detailed response!

You're welcome.

> Can I say:
> Create INDEX c on myTable(c);

Well, technically yes you can.  But to keep your code clear I would not have an index name the same as a column name.  For instance, if you start trusting index names like that you're going to be very confused if you accidentally do

Create INDEX c on myTable(b)

Your index names can be anything you like, but SQL itself never looks at the name of the index, just which table and columns it indexes.  The only time SQL cares about the name of the index is if you later issue a 'DROP INDEX' command and have to get the name right so it knows which index to drop !  So I deliberately pick names for indexes that I'd never pick for a column.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bullzip ms Access to MySql

veneff
Simon,

Thanks!

Vance

on Mar 07, 2013, Simon Slavin <[hidden email]> wrote:

>
>
>On 7 Mar 2013, at 5:51pm, [hidden email] wrote:
>
>> Simon,
>>
>> Thanks for the detailed response!
>
>You're welcome.
>
>> Can I say:
>> Create INDEX c on myTable(c);
>
>Well, technically yes you can.  But to keep your code clear I would not have an index
>name the same as a column name.  For instance, if you start trusting index names
>like that you're going to be very confused if you accidentally do
>
>Create INDEX c on myTable(b)
>
>Your index names can be anything you like, but SQL itself never looks at the name
>of the index, just which table and columns it indexes.  The only time SQL cares about
>the name of the index is if you later issue a 'DROP INDEX' command and have to get
>the name right so it knows which index to drop !  So I deliberately pick names for
>indexes that I'd never pick for a column.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How can I improve this query?

James K. Lowden
In reply to this post by Yuzem
On Wed, 6 Mar 2013 07:20:51 -0800 (PST)
Yuzem <[hidden email]> wrote:

> Given the same schema, I can list all genres with one movie by genre:
> SELECT genres, movies FROM genres GROUP BY genres;

        HAVING count(movie) = 1

The HAVING clause does for aggregates what WHERE does for data.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users