How many rows can a MYSQL Table store?

[MatthewBOnline]

Hi,

I am planning to create a database that stores data every minute of the year. This may be too many rows for a PHP MYAdmin table?

I also thought if I create a big database it may be better to store the data every 1 month. (12 smaller tables). I would probably only access small portions of the table so a smaller table data would load faster, right?

Matt.

[Mittineague]

I don’t know about a “PHP MYAdmin table” but if you mean MySQL, it depends
Limits on Table Size
Limits on Table Column Count and Row Size

Every minute of the year? Hmmm, that’s roughly >= 525600 rows

[Noppy]

We have a table of UK postcodes which is around 2 Million entries. doesn’t take all that long to return on a query. You’d probably be better off with 1 large table. depends how many columns you are going to run and what you are storing. e.g i probably wouldn’t want to store 0.5 million blob fields but simple number/text will probably be ok. Just make sure you get your index’s correct to make it as fast as possible to search.

[MatthewBOnline]

OK Noppy,

but what do you mean about “get your index’s correct”?

Do you mean have an ID with a unique key?

[Noppy]

to be honest i am not the greatest when it comes to database structure. My understanding is that you should have a primary index on a unique id column and then other index’s on columns that you are going to be searching on. e.g for my postcode table i’d have an index on the postcode field which can actually be the primary unique index as the postcodes should all be different. Basically it means when you do a query it doesn’t have to search every bit of data like in a book you can look at the contents rather than having to read every page. This should give you a better explanation than i can http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

[DarthGuido]

@Noppy

you should have a primary index on a unique id column

Only if a unique id column is needed.

@Noppy

and then other index’s on columns that you are going to be searching on

Yes, it depends on the queries you need to perform. Remember that you can also make composite keys.

[JamesHollis]

If your data is inherently relational, and subject to queries that work well with SQL, you should be able to scale to hundreds of millions of records without any crazy hardware requirements.

You will need to invest in indexing, query tuning, and making the occasional sacrifices to the relational model in the favour of speed. You should at least nod to performance when designing tables – preferring integers to strings for keys, for instance.