Would MySQL handle Millions of records tables

[Mathieu Pagé]

Hi,

I already found on the MySQL web site that some users did have good
results with tables of some millions records.

But, what I want to do is store some hundreds millions records in a
table. 190 millions to start and maybe a lot more after. Does someone
already use MySQL with such a quantity of data ?

I was also asking myself how a simple SELECt query like this one below
would perform on such a table.

SELECT * FROM MyTable WHERE MyPrimaryKeyFie ld = Value;

I think that with indexes this shoudl be fast despite the fact that
the table is huge.

An other kind of query i'd like to do is :

SELECT Count(*) FROM MyTable WHERE ABooleanField = True;

I expect this one to take several minutes, maybe hours. Is my guess
right or could it be lot faster/slower ?

In advance, thanks for your help,

Mathieu Pagé

[Paul Bramscher]

Mathieu Pagé wrote:

Hi,

I already found on the MySQL web site that some users did have good
results with tables of some millions records.

But, what I want to do is store some hundreds millions records in a
table. 190 millions to start and maybe a lot more after. Does someone
already use MySQL with such a quantity of data ?

I was also asking myself how a simple SELECt query like this one below
would perform on such a table.

SELECT * FROM MyTable WHERE MyPrimaryKeyFie ld = Value;

I think that with indexes this shoudl be fast despite the fact that the
table is huge.

An other kind of query i'd like to do is :

SELECT Count(*) FROM MyTable WHERE ABooleanField = True;

I expect this one to take several minutes, maybe hours. Is my guess
right or could it be lot faster/slower ?

In advance, thanks for your help,

Mathieu Pagé

Depends on the hardware. But if the table is indexed and your fields
are blob or binary, you might achieve a surprisingly decent performance.
For example, a binary tree based index on 190 million rows would only
involve about 28 comparisons. 2^28 = 268,435,456.

Your real hit would come in rebuilding the index if the data is subject
to a lot of change.

[Paul Bramscher]

Paul Bramscher wrote:

Mathieu Pagé wrote:

Hi,

I already found on the MySQL web site that some users did have good
results with tables of some millions records.

But, what I want to do is store some hundreds millions records in a
table. 190 millions to start and maybe a lot more after. Does someone
already use MySQL with such a quantity of data ?

I was also asking myself how a simple SELECt query like this one
below would perform on such a table.

SELECT * FROM MyTable WHERE MyPrimaryKeyFie ld = Value;

I think that with indexes this shoudl be fast despite the fact that
the table is huge.

An other kind of query i'd like to do is :

SELECT Count(*) FROM MyTable WHERE ABooleanField = True;

I expect this one to take several minutes, maybe hours. Is my guess
right or could it be lot faster/slower ?

In advance, thanks for your help,

Mathieu Pagé

Depends on the hardware. But if the table is indexed and your fields
are blob or binary, you might achieve a surprisingly decent performance.

I meant if your fields are NOT blob or binary. Slip of the key there.
For example, a binary tree based index on 190 million rows would only
involve about 28 comparisons. 2^28 = 268,435,456.

Your real hit would come in rebuilding the index if the data is subject
to a lot of change.