2

Currently I have lots of rows in mysql db

venue_id
venue_name
venue_location
venue_geolocation
venue_type
venue_url
venue_manager
venue_phone
venue_logo
venue_company
venue_zip
venue_vat
venue_visible

Would it be more efficient to store most of the data in one array and in one row like venue_data. Then it would leave only 3 rows venue_id, venue_data, venue_visible. Then in my application I could explode that array. Would it save time, server load?

abxstract
  • 319
  • 3
  • 11
  • When you mean stored as array, do you mean stapling different kinds of information together into a single database column (string)? – ryanyuyu Jan 06 '15 at 21:54
  • The minimal amount of time it might (doubtfully) save would not be worth the loss of readability, distinct data types, ability to query specific columns, and a host of other benefits you gain from using one column per piece of information, not to mention you then add time parsing messy strings into an array instead of simply using $result->fetch_assoc(). I would advise keeping your current setup. – Brian Jan 06 '15 at 22:01

2 Answers2

3

Storing the values as array (concatenating different values into a string?) is definitely a bad idea because:

  • You will loose the readability,
  • you won't be able to easily search on concatenated columns,
  • you cannot index these columns properly.

Furthermore it does not have an impact to the performance - see also Is there a performance decrease if there are too many columns in a table?

If you are unhappy with the many columns, you should consider normalizing (DB Normalization) your db schema.

Community
  • 1
  • 1
agim
  • 1,841
  • 12
  • 19
1

You must ask yourself whether the amount of time and space you 'might' save is worth the cost.

Consider:

  • Combining columns into one will still have a comparable length as all of them separately
  • More space could potentially be saved by using appropriately sized data types
  • Disk space is cheap
  • Having distinct columns gives you the power to query any of those columns
  • Distinct columns also allows you to easily add or remove columns at a later date without having to re-construct every row's combined column
  • Distinct columns you can use $result->fetch_assoc() to immediately get your result row in an array, vs. spending processing time parsing a complex string
  • Parsing such a string may be prone to errors that selecting specific columns is not
  • You can add foreign key constraints and indexes on individual columns which would not work if you combined them
  • You can easily search on distinct columns, but not if you combine them

I can think of plenty more reasons why distinct columns are a better choice than trying to optimize code in a way that likely will not even save you any time. The query may be a few milliseconds faster, but you lost that time processing the string.

Brian
  • 336
  • 1
  • 9