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