Re: PostgreSQL Indexing versus MySQL - Mailing list pgsql-general

I had this same problem not long ago, and it caused me go with
PostgreSQL over MySQL on a project.  The problem you're describing
does not exist in PostgreSQL.  Creating a new index on a table does
not require recreating the other indexes.  MySQL also has the same
problem if you add or drop columns, all indexes need to be recreated.
Again, not a problem in PSQL.  You'll find times similar to Oracle and
MS SQL (I've never directly compared them, but they feel about the
same).


Regards,


Adam Ruth



On Feb 11, 2004, at 1:58 PM, Chris Fossenier wrote:


<excerpt><fontfamily><param>Arial</param><smaller>I currently have a
client with a database that must hold 125 million records and all
tallied about 250 fields.</smaller></fontfamily>

 

<fontfamily><param>Arial</param><smaller>The database has been
normalized and indexed appropriately.</smaller></fontfamily>

 

<fontfamily><param>Arial</param><smaller>If any of you have worked
with MySQL, you will have discovered that indexing is very limited.
You can only have one index file per table. The indexing process
actuallly creates a full copy of the original table and once you get
above 2 indexes with 125million records, it is extremely slow.</smaller></fontfamily>

 

<fontfamily><param>Arial</param><smaller>Should I even bother trying
PostgreSQL to resolve this issue?</smaller></fontfamily>

 

<fontfamily><param>Arial</param><smaller>We can generate the same
indexes in MS SQL and Oracle in a fraction of the amount of time when
held up to MySQL.</smaller></fontfamily>

 

<fontfamily><param>Arial</param><smaller>Thanks</smaller></fontfamily>

 

<fontfamily><param>Arial</param><smaller>Chris.</smaller></fontfamily>

</excerpt>I had this same problem not long ago, and it caused me go with
PostgreSQL over MySQL on a project.  The problem you're describing does
not exist in PostgreSQL.  Creating a new index on a table does not
require recreating the other indexes.  MySQL also has the same problem
if you add or drop columns, all indexes need to be recreated.  Again,
not a problem in PSQL.  You'll find times similar to Oracle and MS SQL
(I've never directly compared them, but they feel about the same).

Regards,

Adam Ruth


On Feb 11, 2004, at 1:58 PM, Chris Fossenier wrote:

> I currently have a client with a database that must hold 125 million
> records and all tallied about 250 fields.
>  
> The database has been normalized and indexed appropriately.
>  
> If any of you have worked with MySQL, you will have discovered that
> indexing is very limited. You can only have one index file per table.
> The indexing process actuallly creates a full copy of the original
> table and once you get above 2 indexes with 125million records, it is
> extremely slow.
>  
> Should I even bother trying PostgreSQL to resolve this issue?
>  
> We can generate the same indexes in MS SQL and Oracle in a fraction of
> the amount of time when held up to MySQL.
>  
> Thanks
>  
> Chris.