More efficient to have more columns or more rows?

Question

I'm currently redesigning a database which could contain a lot of data - I have the option to either include a number of different columns in the database or use a lot of rows instead. It's probably easier if I did some kind of outline below:

item_id | user_id | title | description | content | category | template | comments | status
-------------------------------------------------------------------------------------------
1       | 1       | ABC   | DEF         | GHI     | 1        | default  | 1        | 1
2       | 1       | ZYX   |             | QWE     | 2        | default  | 0        | 1
3       | 1       | A     |             | RTY     | 2        | default  | 0        | 0
4       | 2       | ABC   | DEF         | GHI     | 3        | custom   | 1        | 1
5       | 2       | CBA   |             | GHI     | 3        | custom   | 1        | 1

Versus something in the following structure:

item_id | user_id | attribute   | value
---------------------------------------
1       | 1       | title       | ABC
1       | 1       | description | DEF
1       | 1       | content     | GHI
...     | ...     | ...         | ...

I may want to create additional attributes in the future (50 for arguments sake) - so there could be a lot of empty cells if using multiple columns. The attribute names would be reused, where possible, across different types of content - say a blog entry, event, and gallery - title would easily be reused.

So my question is, is it more efficient to use multiple columns or multiple rows - in terms of query speed and disk space. Or would you instead recommend relationship tables, so there's a table for blogs, a table for events, etc. I'm just trying to come up with an easily expandable solution, where I ideally do not want to create a table for every kind of content as I'm thinking of developers creating new kinds of content via an app/API system (with attributes being tightly controlled).

Supplementary Question if Multiple Rows

How could I, in MySQL, convert multiple rows into a usable column format (I guess temporary tables) - so I could do some filtering by content type, as an example.

Answer 1

Basically, mysql has a variable row length as long as one does not change the on a per table level. Thus, empty cols will not use any space (well, almost).

But with blobs or text columns, it might be better to normalize those, as these may have large data to store and this needs to be read / skipped every time a table is scanned. Even if the column is not in the result set and you're doing queries outside of an index, it will take it's time on a large amount of rows.

As a good practice I think it will be fast to put all administrative and often used cols in one table and normalize all the rest. A kind of "vertical" design as in your second example will be complex to read and as soon as you work with temporary tables you will run in to performance issues sooner or later.