Is it true that columns number does not affect database performance but rows number does?

Question

As in title.

I'm considering to use separate tables with JOIN or use single table, basically I'm worried about the performance.

Thank you.

Answer 1

The number of columns returned by a query can affect performance -- it is recommended to only bring back the columns you need.

It is better to break off repeated information into separate tables for other reasons (known as normalisation) -- for example, if you had candidates for a job, you don't want to have many columns on the job for each applicant - you'd be better to have a table of applicants, a table of jobs and a table of applications that link the two.

Having a wide table can affect performance in its own right as well, as each record is bigger, which means more disc seeks to scan through the table (there are caveats here that it depends greatly on the data types used)

Answer 2

The total number of columns in a table could drastically affect performance. A wide table could involve loading large amounts of data for each row, unless you are careful to only bring back columns you need in each query.

Besides, a large number of columns in a table is probably an indication of a design flaw and lack of normalization.

The number of rows is less of an issue if your indexes are setup correctly.

Answer 3

No, column numbers affect performance too. More columns means more data that needs to be processed. More columns affect the layout on the drive, which might affect the performance.

All this depends a lot on your data, your queries and how you've tuned your database and tables (e.g. proper indexes according to your queries.

Start profiling and testing with your actual data and queries before you worry about performance though, if you're just guessing you'll guess wrongly.

Answer 4

This is called vertical partitioning. It can reduce I/O if you can separate less frequently used columns into their own table but will likely add overhead if most of your queries end up joining the tables. Especially if both tables are on the same disc.

Answer 5

A very wide table (dozens to 100's of columns) will definitely cause performance problems if you do a select *... or try to update / insert a row and change every column, or if many columns are indexed.

Answer 6

Both column number and row numbers affect performance.

If you are worried about performance you may want to create views with only the columns you are using for the join. Another option is to create indexes but only use indexes if the table doesn't get updated frequently.

Also you should not have to worry about performance unless you have more than 1000 records or over 15 columns.