0

Using PHP, I am building an application that is MySQL database resource heavy, but I also need it's data to be very flexible. Currently there are a number of tables which have an array of different columns (including some text, longtext, int, etc), and in the future I would like to expand on the number of columns of these tables, whenever new data-groups are required.

My question is, if I have a table with, say, 10 columns, and I expand this to 40 columns in the future, would a SQL query (via PHP) be slowed down considerably?

As long as the initial, small query that is only looking up the initial 10 columns is not a SELECT-all (*) query, I would like to know if more resources or processing is used because the source table is now much larger.

Also, will the database in general run slower or be much larger due to many columns now constantly remaining as NULL values (eg, whenever a new entry that only requires the first 10 columns is inserted)?

Rhys
  • 1,581
  • 2
  • 14
  • 22
  • Regarding flexible data warehousing, you may be interested in this SO question: http://stackoverflow.com/questions/3455168/case-studies-or-examples-of-high-throughput-services-with-highly-dynamic-data – cbednarski Dec 06 '10 at 04:41
  • You need to redesign your lame table structure. New data groups should be handled as additional rows in linked tables, not as additional columns. It's matter of database architecture, not performance. – Your Common Sense Dec 06 '10 at 06:22
  • Nothing to upvote here. Just another "performance" question out of general ignorance. – Your Common Sense Dec 06 '10 at 06:23

5 Answers5

1

Likely, the best solution would be to create a new table with the additional fields and JOIN the tables when necessary. The original table remains unchanged, keeping it's speed, but you can still get to the extra fields.

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
1

MyISAM and InnoDB behave differently in this regard, for various reasons.

For instance, InnoDB will partition disk space for each column on disk regardless of whether it has data in it, while MyISAM will compress the tables on disk. In a case where there are large amounts of empty columns, InnoDB will be wasting a lot of space. On the other hand, InnoDB does row-level locking, which means that (with caveats) concurrent read / writes to the same table will perform better (MyISAM does a table-level lock on write).

Generally speaking, it's probably not a good idea to have many columns in one table, particularly for volatility reasons. For instance, in InnoDB (possibly MyISAM also?), re-arranging columns or changing types of columns (i.e. varchar 128 -> varchar 255) in the middle of a table requires that all data in columns to the right be moved around on disk to make (or remove) space for the altered column.

With respect to your overall database design, it's best to aim for as many columns as possible to be not null, which saves space (you don't need the null flag on the column, and you don't store empty data) and also increases query and index performance. If many records will have a particular column set to null, you should probably move it to a foreign key relationship and use a JOIN. That way disk space and index overhead is only incurred for records that are actually holding information.

cbednarski
  • 11,718
  • 4
  • 26
  • 33
0

Optimization is not a trivia question. Nothing can be predicted.

In general short answer is: yes, it will be slower (because DBMS at least need to read from the disk and send more data, obviously).

But, it is very dependent on each particular case how much slower it will be. You can either even don't see the difference, or get it 10x times slower.

zerkms
  • 249,484
  • 69
  • 436
  • 539
0

In all likelihood, no it won't be slowed down considerably.

However, a better question to ask is: Which method of adding more fields results in a more elegant, understandable, maintainable, cost effective solution?

Usually the answer is "It depends." It depends on how the data is accessed, how the requirements will change, how the data is updated, and how fast the tables grow.

wallyk
  • 56,922
  • 16
  • 83
  • 148
-1

you can divide one master table into multiple TRANSACTION tables so you will get much faster result than you getting now. and also make the primary key as UNIQUE KEY also in all the transaction as well as master tables. its really help you to make your query faster.

Thanks.

Chandresh M
  • 3,808
  • 1
  • 24
  • 48