Back to Table of Contents

Brain Teasers Database Design

Indexes


You can see how indexes really speed things up. Here are some SQL queries run on my database before and after creating indexes. For these tests I loaded my database with a bunch of fake data to simulate a large database. This is why the answers to these queries return jibberish.

SQL> select id, title, rating from teaser T1 where not exists ( select * from tea
ser T2 where T1.rating < T2.rating);

        ID TITLE
---------- ----------------------------------------------------------------
    RATING
----------
      2117 @emi31UKeZxiedztr3uSTdLFa~tzMnamO
  9.995422


Elapsed: 00:00:00.58
SQL> create index rankindex on teaser(rating);
Now I create an index on 'rating'. Notice how the elapsed time is cut nearly in half for the same query.
SQL> select id, title, rating from teaser T1 where not exists ( select * from tea
ser T2 where T1.rating < T2.rating);

        ID TITLE
---------- ----------------------------------------------------------------
    RATING
----------
      2117 @emi31UKeZxiedztr3uSTdLFa~tzMnamO
  9.995422


Elapsed: 00:00:00.29





SQL> delete from account where lastlogin < sysdate - 60;

969 rows deleted.

Elapsed: 00:00:05.79

SQL> create index lastloginindex on account(lastlogin);

Now reload the data and make an index. See how much of a difference it makes.

SQL> delete from account where lastlogin < sysdate - 60;

968 rows deleted.

Elapsed: 00:00:04.20





SQL> select id from teaser where id in (select id from submitted where approved = 'no');

        ID                                                                      
----------                                                                      
         4                                                                      
         6                                                                      
         7                                                                      
         8                                                                      
        10                                                                      
        16                                                                      
        17                                                                      
        18                                                                      
        20                                                                      
        22                                                                      
        23       
        
****~1000 more lines of data ****
      
996 rows selected.

Elapsed: 00:00:00.59
SQL> create index approvedindex on submitted(approved);

I made this index, but it didn't really speed things up that much. Ohh well.

SQL> select id from teaser where id in (select id from submitted where approved = 'no');
      
        ID                                                                      
----------                                                                      
         4                                                                      
         6                                                                      
         7                                                                      
         8                                                                      
        10                                                                      
        16                                                                      
        17                                                                      
        18                                                                      
        20                                                                      
        22                                                                      
        23   
        
****~1000 more lines of data ****

996 rows selected.

Elapsed: 00:00:00.50




Back to Table of Contents


by Jake Olefsky - June 2000