0

I am a mysql novice and have been handed task of creating a table in mysql - we don't know the column name or the data type or the length of the data type. We get data, xml, files from various reporting engines and want to load it into this table

I was told that I could make all columns to be varchar(512) and utf-8 and that the performance of any search wont be affected.

The # of columns can be from 10-200 and even if i had a UI to allow someone to actually set the right data type and length, i feel that it would be very hard for someone to actually configure 200 columns to the right data type and length

As i don't have much data to load I cannot confirm my managers suggestion of varchar(512) and utf 8, as some fields contain only name and description so my issue is in that case it is a waste of space to decalre it as varchar(512) when varchar(32) or something like that could work

Is there an efficient way around this on mysql 5.5+

user3865642
  • 25
  • 7
  • If i look at the documentation, it states that I could have 85 columns of `varchar(255)` and `utf -8`. http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html – user3865642 Sep 04 '14 at 14:55
  • That's a good point, actually. But a bigger question is still, will this approach be useful? If you don't have any idea of what data/structure you will get, how can you plan your schema at all? In that case you might be better off storing the received data/XML (or convert it to JSON) as-is, in a single column. Or you can look into a NoSQL solution. – DCoder Sep 04 '14 at 15:16
  • its a tab delimited file, so convert that into a json and store that. if i have to perform a search then only way would be to serialize that string and then perform search on it – user3865642 Sep 04 '14 at 15:31

1 Answers1

0

Are you sure you should be using MySQL for this? If the data is largely unstructured, then you might want to consider a NoSQL database.

What are you going to do with the data, once it's in MySQL?

Daniel Scott
  • 7,418
  • 5
  • 39
  • 58
  • Apparently they have a plan to do some reporting of some sort based on user search criteria. basically they want to store data from tab delimited file on a mysql table and search that instead of searching the tab delimited file – user3865642 Sep 04 '14 at 15:34
  • i did suggest nosql but not a viable option – user3865642 Sep 04 '14 at 15:35
  • In that case, I guess you don't have much choice. It sounds like they're trying to force unstructured data into a database. So, it's not really a surprise that it's not 'fitting' very well. As in the other comments - VARCHAR isn't a waste of space. – Daniel Scott Sep 04 '14 at 15:44