Mysql – Is it better to have large tables or many tables (MySQL)

MySQLscalability

In terms of performance, is it advisable to have only a few tables with a lot of rows or a lot of tables with only a few rows? In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to? Which version would be better in terms of scalability? I am using PHP to access the data and I only need to get data for one or two groups per page load. I am mainly concerned with the speed of the system when there is a lot of data being stored. Thanks.

Best Answer

In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to?

Define "very, very large".
Millions of rows? That's large[-ish]. Anything less than that really isn't.

If you went with multiple tables, how would you identify them?
You don't want to use Group Names as Table Names, because someone will create a Group containing a character that can't be used as such and break everything. So you'd wind up with another table that links each table name to its Group name using some other, arbitrary identifier.

Assuming you did this; how would you then write any sort of query that goes across all of those tables, which you might need to do for Administrative purposes. Let's assume that the database is powerful enough to be able to join or union that many tables in one query? (MySQL, for example, used to be limited to 61 such tables).

Basically, the table-per-thing model is generally a Bad Idea and should be avoided.

Create a single Groups table, with a unique identifier for each Group and index it sensibly for the queries that you intend to run against it.

Related Solutions

Mysql – Storing a large number of small size entries in MySQL

It depends on exactly what you're trying to do with the data -- if it's only being used for graphing, and you don't need high resolution the further out it goes (eg, you're not trying to plot a graph with full temporal resolution for a day from 1 year ago), you might actually want to look at RRDTool rather than a relational database.

If you're going to ever need to do analysis of the values (how often does a place get above 80°F?), you'll want to store discrete values, not an XML structure; but you could also use flat file stuctures that are meant for dealing with this type of data (eg, CDF, NetCDF ... maybe even HDF )

update :

I'd store each time as a separate record, as it makes it easier to adjust the granularity when graphing. For instance, to extract the high/low/mean for each hour:

SELECT   min(date_obs),min(temp),max(temp),avg(temp)
FROM     observations
WHERE    date_obs between ...
GROUP BY floor(unix_timestamp(date_obs)/3600)

Also, it allows you to change the cadence for the measurements without needing to change the table structure.

Mysql – One table with many rows or many tables with a few rows

Assuming that you are talking about tables containing the same kinds of entities, you typically want to have one table.

You would not have any performance differences and a whole lot of management differences between the two approaches, with the single table being easier to manage. Typically large tables do not have performance overheads compared to smaller versions of the same when properly indexed, since the index seek time grows slowly compared to the row growth.

In a normalized design, you will have different tables for different entities or relations, but as far as partitioning (either supported with a DBMS feature or manually with separate tables), that usually is necessary when you have certain requirements, like granularity of backup or data loading/unloading on a partition basis.

Best Answer

Related Solutions

Mysql – Storing a large number of small size entries in MySQL

Mysql – One table with many rows or many tables with a few rows

Related Question