IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

IWETHEY Home / IWETHEY Board / Databases Forum / 20 hours later, still indexing
Post #20,144
by broomberg
12/3/01 8:15:59 PM
Reply
New 20 hours later, still indexing
I've got 1/2 the indexes on the PgSQL done. I'll try
to let the MySQL complete, and then compare query speeds.

If the MySQL takes 24 hours on this box, I can double the
CPU to it, if the query speeds make it worthwhile.
Post #20,165
by broomberg
12/3/01 11:03:46 PM
Reply
New Wheeee - Done!!!
select name_full from ind_1101
where state = 'NJ' and name_last like 'ROOMBE%'

4 rows in set (0.57 sec)

1/2 second for a dual index lookup out of 50 million records.

I'll whip up a bench to simulate a couple hundred variations
an exec them same time to make sure it scales.

PostGreSQL isn't done 'vacuuming' yet, so the comparison isn't
ready, but it seems MUCH slower, and the indexes ARE analyzed
at this point. About 30 seconds for the same query.

MySQL is the keeper. Double the hardware, and the load can be
done in a single day. $3,000 in hardware saves $80,000 in Oracle.

I'll also modify the load:

create table
create indexes
disable indexes
load data
enable indexes

I suspect that will be faster.

Also, MySQL took 1/2 the space for the tables than PostGreSQL.
     MySQL vs Oracle question - (broomberg) - (23) - Dec. 2, 2001, 09:37:16 AM EST
         Other things to consider: - (admin) - (14) - Dec. 2, 2001, 11:45:06 AM EST
             Re: Other things to consider: - (broomberg) - (6) - Dec. 2, 2001, 12:08:04 PM EST
                 Interbase "seen" as going away... - (admin) - (5) - Dec. 2, 2001, 03:34:21 PM EST
                     Re: Interbase "seen" as going away... - (broomberg) - Dec. 2, 2001, 07:41:07 PM EST
                     PostGreSQL setup/complexity - (broomberg) - (3) - Dec. 3, 2001, 08:18:29 PM EST
                         Yep. - (admin) - (2) - Dec. 3, 2001, 08:58:00 PM EST
                             Re: Yep. - (broomberg) - (1) - Dec. 3, 2001, 09:42:28 PM EST
                                 Shouldn't need one right away. - (admin) - Dec. 3, 2001, 11:07:27 PM EST
             Wierd create index behaviour - (broomberg) - (6) - Dec. 2, 2001, 12:36:13 PM EST
                 index create - (boxley) - (5) - Dec. 2, 2001, 08:04:05 PM EST
                     Re: index create - (broomberg) - (4) - Dec. 2, 2001, 09:09:04 PM EST
                         Well, Sybase still has some money - (tonytib) - Dec. 2, 2001, 09:39:52 PM EST
                         Re: index create - (broomberg) - (2) - Dec. 2, 2001, 10:27:47 PM EST
                             1.5 hours to create index - (broomberg) - Dec. 2, 2001, 10:53:57 PM EST
                             That's why I'd second the Interbase recommendation - (tseliot) - Dec. 3, 2001, 03:28:05 PM EST
         Re: MySQL vs Oracle question - (wharris2) - (2) - Dec. 3, 2001, 12:47:34 AM EST
             Readonly - (broomberg) - (1) - Dec. 3, 2001, 07:09:48 AM EST
                 Readonly? Hell, then, full speed ahead! -NT - (wharris2) - Dec. 6, 2001, 07:41:13 PM EST
         14 hours later, still indexing - (broomberg) - (2) - Dec. 3, 2001, 02:08:57 PM EST
             20 hours later, still indexing - (broomberg) - (1) - Dec. 3, 2001, 08:15:59 PM EST
                 Wheeee - Done!!! - (broomberg) - Dec. 3, 2001, 11:03:46 PM EST
         New design - (broomberg) - (1) - Dec. 5, 2001, 08:58:34 PM EST
             Happy Ending! - (Meerkat) - Dec. 6, 2001, 06:01:45 PM EST

Well, all right then.
51 ms