-1

I have written an application that parses imdb (from their public ftp files) data and stores it into data base of my choice.

2 of my database tables have over 1 million records (2.316.952 and 1.392.866). My application's purpose is to select 1 movie (which data resides in both tables) every time user clicks a button. So my question would be, what what be the most efficient way to store my data?

I tried PostgreSQL and I was pretty happy with it. My only problem that it's pretty hard to find cheap hosting with PostgreSQL installed.

I tried MySQL, but for some reason it's so much slower than Postgres. If Postgres select would take ~3.5s, MySQL would take over 10s. Any ideas why could that be? MySQL used innodb engine.

To summarise: Can anyone think of a better way to store the data? And why is MySQL slower?

Edit:

Now that's a bit of a problem, I used hibernate/JPA and I used dynamic queries (hibernate api generates all the SQL).

As for tables:

CREATE TABLE "movies" (
    "id" INTEGER NOT NULL,
    "name" TEXT NULL DEFAULT NULL,
    "rank" DOUBLE PRECISION NOT NULL,
    "releaseyear" INTEGER NULL DEFAULT NULL,
    "summary" TEXT NULL DEFAULT NULL,
    "votes" BIGINT NOT NULL,
    PRIMARY KEY ("id")
)

CREATE TABLE "movies_genres" (
    "moviejpa_id" INTEGER NOT NULL,
    "genres_id" INTEGER NOT NULL
)

CREATE TABLE "genres" (
    "id" INTEGER NOT NULL,
    "name" VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY ("id")
)

Tables are also created by hibernate/jpa.

Data example: movies movies_genres genres

Edit (managed to print queries that hibernate generates):

select
    moviejpa0_.id as col_0_0_ 
from
    movies moviejpa0_ 
inner join
    movies_genres genres1_ 
        on moviejpa0_.id=genres1_.MovieJpa_id 
inner join
    genres genrejpa2_ 
        on genres1_.genres_id=genrejpa2_.id 
where
    (
        genrejpa2_.id in (
            ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
        )
    ) 
    and (
        moviejpa0_.rank between 0.0 and 10.0
    ) 
    and (
        moviejpa0_.votes between 0 and 1814484
    )

select
    moviejpa0_.id as id1_4_0_,
    moviejpa0_.name as name2_4_0_,
    moviejpa0_.rank as rank3_4_0_,
    moviejpa0_.releaseYear as releaseY4_4_0_,
    moviejpa0_.summary as summary5_4_0_,
    moviejpa0_.votes as votes6_4_0_,
    genres1_.MovieJpa_id as MovieJpa1_5_1_,
    genrejpa2_.id as genres_i2_5_1_,
    genrejpa2_.id as id1_3_2_,
    genrejpa2_.name as name2_3_2_ 
from
    movies moviejpa0_ 
left outer join
    movies_genres genres1_ 
        on moviejpa0_.id=genres1_.MovieJpa_id 
left outer join
    genres genrejpa2_ 
        on genres1_.genres_id=genrejpa2_.id 
where
    moviejpa0_.id=?

Edit:

Postgre plans: (it's been a while since I did anything with data bases directly, I used explain SQL, don't know if that is still correct)

First SQL:

Nested Loop Left Join  (cost=0.43..39238.32 rows=2 width=1108)
Join Filter: (genres1_.genres_id = genrejpa2_.id)
->  Nested Loop Left Join  (cost=0.43..39222.37 rows=2 width=588)
->  Materialize  (cost=0.00..12.10 rows=140 width=520)
    Join Filter: (moviejpa0_.id = genres1_.moviejpa_id)
    ->  Seq Scan on movies_genres genres1_  (cost=0.00..39213.90 rows=2 width=8)
    ->  Seq Scan on genres genrejpa2_  (cost=0.00..11.40 rows=140 width=520)
    ->  Index Scan using movies_pkey on movies moviejpa0_  (cost=0.43..8.45 rows=1 width=580)
    Index Cond: (id = 916424)
    Filter: (moviejpa_id = 916424)

Second SQL:

Hash Join  (cost=84978.62..150810.17 rows=579193 width=4)
  Hash Cond: (genres1_.moviejpa_id = moviejpa0_.id)
  ->  Hash Join  (cost=17.96..47920.43 rows=579238 width=4)
        Hash Cond: (genres1_.genres_id = genrejpa2_.id)
        ->  Seq Scan on movies_genres genres1_  (cost=0.00..33421.52 rows=2316952 width=8)
        ->  Hash  (cost=17.53..17.53 rows=35 width=4)
              ->  Seq Scan on genres genrejpa2_  (cost=0.00..17.53 rows=35 width=4)
                    Filter: (id = ANY ('{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34}'::integer[]))
  ->  Hash  (cost=62116.86..62116.86 rows=1392384 width=4)
        ->  Seq Scan on movies moviejpa0_  (cost=0.00..62116.86 rows=1392384 width=4)
              Filter: ((rank >= '0'::double precision) AND (rank <= '10'::double precision) AND (votes >= 0) AND (votes <= 1814484))

MySQL plans:

First sql:

<row>
    <field name="id">1</field>
    <field name="select_type">SIMPLE</field>
    <field name="table">moviejpa0_</field>
    <field name="partitions" xsi:nil="true" />
    <field name="type">const</field>
    <field name="possible_keys">PRIMARY</field>
    <field name="key">PRIMARY</field>
    <field name="key_len">4</field>
    <field name="ref">const</field>
    <field name="rows">1</field>
    <field name="filtered">100.00</field>
    <field name="Extra" xsi:nil="true" />
</row>
<row>
    <field name="id">1</field>
    <field name="select_type">SIMPLE</field>
    <field name="table">genres1_</field>
    <field name="partitions" xsi:nil="true" />
    <field name="type">ref</field>
    <field name="possible_keys">FK1oevm2ns4a61icpilhyq1dwr7</field>
    <field name="key">FK1oevm2ns4a61icpilhyq1dwr7</field>
    <field name="key_len">4</field>
    <field name="ref">const</field>
    <field name="rows">1</field>
    <field name="filtered">100.00</field>
    <field name="Extra" xsi:nil="true" />
</row>
<row>
    <field name="id">1</field>
    <field name="select_type">SIMPLE</field>
    <field name="table">genrejpa2_</field>
    <field name="partitions" xsi:nil="true" />
    <field name="type">eq_ref</field>
    <field name="possible_keys">PRIMARY</field>
    <field name="key">PRIMARY</field>
    <field name="key_len">4</field>
    <field name="ref">test.genres1_.genres_id</field>
    <field name="rows">1</field>
    <field name="filtered">100.00</field>
    <field name="Extra" xsi:nil="true" />
</row>

Second sql:

        <field name="id">1</field>
    <field name="select_type">SIMPLE</field>
    <field name="table">moviejpa0_</field>
    <field name="partitions" xsi:nil="true" />
    <field name="type">ALL</field>
    <field name="possible_keys">PRIMARY</field>
    <field name="key" xsi:nil="true" />
    <field name="key_len" xsi:nil="true" />
    <field name="ref" xsi:nil="true" />
    <field name="rows">1321403</field>
    <field name="filtered">1.23</field>
    <field name="Extra">Using where</field>
</row>
<row>
    <field name="id">1</field>
    <field name="select_type">SIMPLE</field>
    <field name="table">genres1_</field>
    <field name="partitions" xsi:nil="true" />
    <field name="type">ref</field>
    <field name="possible_keys">FKabwobqnegu888274nercpwc9p,FK1oevm2ns4a61icpilhyq1dwr7</field>
    <field name="key">FK1oevm2ns4a61icpilhyq1dwr7</field>
    <field name="key_len">4</field>
    <field name="ref">test.moviejpa0_.id</field>
    <field name="rows">1</field>
    <field name="filtered">100.00</field>
    <field name="Extra">Using where</field>
</row>
<row>
    <field name="id">1</field>
    <field name="select_type">SIMPLE</field>
    <field name="table">genrejpa2_</field>
    <field name="partitions" xsi:nil="true" />
    <field name="type">eq_ref</field>
    <field name="possible_keys">PRIMARY</field>
    <field name="key">PRIMARY</field>
    <field name="key_len">4</field>
    <field name="ref">test.genres1_.genres_id</field>
    <field name="rows">1</field>
    <field name="filtered">100.00</field>
    <field name="Extra">Using index</field>
</row>

EDIT: 2017-06-13 Managed to find out, that this query takes most of the time:

select
    moviejpa0_.id as col_0_0_ 
from
    movies moviejpa0_ 
inner join
    movies_genres genres1_ 
        on moviejpa0_.id=genres1_.MovieJpa_id 
inner join
    genres genrejpa2_ 
        on genres1_.genres_id=genrejpa2_.id 
where
    (
        genrejpa2_.id in (
            ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
        )
    ) 
    and (
        moviejpa0_.rank between 0.0 and 10.0
    ) 
    and (
        moviejpa0_.votes between 0 and 1814484
    )

CREATE TABLE "movies_genres" (
    "genres_id" INTEGER NOT NULL,
    "moviejpa_id" INTEGER NOT NULL,
    INDEX "" ("moviejpa_id"),
    UNIQUE INDEX "UNIQUE" ("genres_id", "moviejpa_id")
)

EXPLAIN:

 QUERY PLAN  
 -  
 Hash Join  (cost=85187.47..150712.33 rows=576153 width=4)  
   Hash Cond: (genres1_.genres_id = moviejpa0_.id)  
   ->  Hash Join  (cost=17.96..47666.67 rows=576165 width=4)  
         Hash Cond: (genres1_.moviejpa_id = genrejpa2_.id)  
         ->  Seq Scan on movies_genres genres1_  (cost=0.00..33244.59 rows=2304659 width=8)  
         ->  Hash  (cost=17.53..17.53 rows=35 width=4)  
               ->  Seq Scan on genres genrejpa2_  (cost=0.00..17.53 rows=35 width=4)  
                     Filter: (id = ANY ('{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34}'::integer[]))  
   ->  Hash  (cost=62271.76..62271.76 rows=1395660 width=4)  
         ->  Seq Scan on movies moviejpa0_  (cost=0.00..62271.76 rows=1395660 width=4)  
               Filter: ((rank >= '0'::double precision) AND (rank <= '10'::double precision) AND (votes >= 0) AND (votes <= 1816967))  

EXPLAIN analyze:

 QUERY PLAN  
 -  
 Hash Join  (cost=85187.47..150712.33 rows=576153 width=4) (actual time=619.514..2039.717 rows=2304659 loops=1)  
   Hash Cond: (genres1_.genres_id = moviejpa0_.id)  
   ->  Hash Join  (cost=17.96..47666.67 rows=576165 width=4) (actual time=0.099..424.101 rows=2304659 loops=1)  
         Hash Cond: (genres1_.moviejpa_id = genrejpa2_.id)  
         ->  Seq Scan on movies_genres genres1_  (cost=0.00..33244.59 rows=2304659 width=8) (actual time=0.025..108.536 rows=2304659 loops=1)  
         ->  Hash  (cost=17.53..17.53 rows=35 width=4) (actual time=0.038..0.038 rows=35 loops=1)  
               Buckets: 1024  Batches: 1  Memory Usage: 10kB  
               ->  Seq Scan on genres genrejpa2_  (cost=0.00..17.53 rows=35 width=4) (actual time=0.013..0.027 rows=35 loops=1)  
                     Filter: (id = ANY ('{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34}'::integer[]))  
   ->  Hash  (cost=62271.76..62271.76 rows=1395660 width=4) (actual time=615.854..615.854 rows=1396310 loops=1)  
         Buckets: 131072  Batches: 32  Memory Usage: 2562kB  
         ->  Seq Scan on movies moviejpa0_  (cost=0.00..62271.76 rows=1395660 width=4) (actual time=0.065..436.606 rows=1396310 loops=1)  
               Filter: ((rank >= '0'::double precision) AND (rank <= '10'::double precision) AND (votes >= 0) AND (votes <= 1816967))  
 Planning time: 0.964 ms  
 Execution time: 2071.123 ms  

2nd query:

 EXPLAIN
 QUERY PLAN  
 -  
 Nested Loop Left Join  (cost=12.92..33.08 rows=2 width=1112)  
   Join Filter: (moviejpa0_.id = genres1_.genres_id)  
   ->  Index Scan using movies_pkey on movies moviejpa0_  (cost=0.43..8.45 rows=1 width=584)  
         Index Cond: (id = 481380)  
   ->  Hash Right Join  (cost=12.49..24.61 rows=2 width=524)  
         Hash Cond: (genrejpa2_.id = genres1_.moviejpa_id)  
         ->  Seq Scan on genres genrejpa2_  (cost=0.00..11.40 rows=140 width=520)  
         ->  Hash  (cost=12.46..12.46 rows=2 width=8)  
               ->  Index Only Scan using movies_genres_pkey on movies_genres genres1_  (cost=0.43..12.46 rows=2 width=8)  
                     Index Cond: (genres_id = 481380) 

 EXPLAIN analyze            
 QUERY PLAN  
 -  
 Nested Loop Left Join  (cost=12.92..33.08 rows=2 width=1112) (actual time=0.883..0.910 rows=3 loops=1)  
   Join Filter: (moviejpa0_.id = genres1_.genres_id)  
   ->  Index Scan using movies_pkey on movies moviejpa0_  (cost=0.43..8.45 rows=1 width=584) (actual time=0.219..0.221 rows=1 loops=1)  
         Index Cond: (id = 481380)  
   ->  Hash Right Join  (cost=12.49..24.61 rows=2 width=524) (actual time=0.655..0.679 rows=3 loops=1)  
         Hash Cond: (genrejpa2_.id = genres1_.moviejpa_id)  
         ->  Seq Scan on genres genrejpa2_  (cost=0.00..11.40 rows=140 width=520) (actual time=0.028..0.034 rows=35 loops=1)  
         ->  Hash  (cost=12.46..12.46 rows=2 width=8) (actual time=0.589..0.589 rows=3 loops=1)  
               Buckets: 1024  Batches: 1  Memory Usage: 9kB  
               ->  Index Only Scan using movies_genres_pkey on movies_genres genres1_  (cost=0.43..12.46 rows=2 width=8) (actual time=0.557..0.563 rows=3 loops=1)  
                     Index Cond: (genres_id = 481380)  
                     Heap Fetches: 3  
 Planning time: 0.689 ms  
 Execution time: 1.026 ms  
  • 3
    The Postgres query optimizer is more advanced then MySQL's optimizer. The more complicated the queries get the bigger the difference between the two. If you want to tune a specific query you will need to show us that query, including all table definitions and indexes defined on the tables and the execution plan. edit your question, do not post code or additional information in comments. – a_horse_with_no_name Jun 5 '17 at 20:51
  • I for one don't believe that PostgreSQL is only 3 times faster than MySQL. Show me the plans. – Evan Carroll Jun 5 '17 at 21:57
  • I just gave 10s to make a point, that it's slower, but I will try to give a correct timing if you want. – CrazySabbath Jun 5 '17 at 22:43
  • Postgre: 3.9s; MySQL: 99s – CrazySabbath Jun 5 '17 at 22:49
  • So MySQL did 10x worse than what you said in the question, which was only 3x worse than Pg. – Evan Carroll Jun 5 '17 at 22:57
3

Using PostgreSQL,

  1. Do not use double quotes
  2. Mark primary keys as such.
  3. rank can be type real.
  4. NULL DEFAULT NULL is totally not needed, ever. That's the default.
  5. on movies_genres add PRIMARY KEY (genres_id, moviejpa_id);
  6. Make sure genre has a PRIMARY KEY of id, it's in your schema, but from your plan it looks like it's not there.
  7. You probably want text instead of varchar(255).

You can quick fix your current set like this,

CREATE INDEX ON movies_genres(MovieJpa_id);
ALTER TABLE genres ADD PRIMARY KEY (id);
ALTER TABLE movies_genres ADD PRIMARY KEY (genres_id, moviejpa_id);
ANALYZE movies_genres;
ANALYZE genres;

Also, you should add the result of show work mem;

  • Can you remind if I have to recreate table data to add new indexes? – CrazySabbath Jun 6 '17 at 9:26
  • What's more, hibernate created the tables (I just copied sql to show here). I will try editing jpa entities, so that jpa would create tables corectly. – CrazySabbath Jun 6 '17 at 9:29
  • Genres table is very small (~34 rows). Whould index still help? – CrazySabbath Jun 6 '17 at 9:34
  • 1
    @CrazySabbath it won't hurt. PostgreSQL isn't that dumb. No you don't have to recreate the tables, just read the section of the answer You can quick fix your current set like this – Evan Carroll Jun 6 '17 at 15:05
  • @Caroll, Great, I will try it and will post the results. – CrazySabbath Jun 6 '17 at 16:03
1

Even 3.5 seconds is too much. You need indexes.

This is a many:many mapping between movies and genres, correct?

CREATE TABLE "movies_genres" (
    "moviejpa_id" INTEGER NOT NULL,
    "genres_id" INTEGER NOT NULL
)

It should be (at least for MySQL):

CREATE TABLE `movies_genres` (
    `moviejpa_id` INT UNSIGNED NOT NULL,
    `genres_id` SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY(moviejpa_id, genres_id),
    INDEX(genres_id, moviejpa_id)
) ENGINE=InnoDB;

See this for more discussion of it.

Please provide EXPLAIN SELECT ... for the MySQL attempt.

  • You are correct about manyToMany. – CrazySabbath Jun 6 '17 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.