The issues in optimizing performance for data storage are primarily hardware/operating-system driven. A partial list of the issues/thoughts/concerns would include:

• A hard-drive generally has seek-times in the milli-second range. RAM has seek-times in the nano-second range. That's a million-to-one difference.
• Most datastores, like MySQL, are optimized for data retrieval. This is because the number of retrievals vs. the number of updates is generally in the 100k-to-one to million-to-one category. (That's based on experience and anecdotal evidence. Various applications have various needs. To compare - I work with two apps right now. One is almost completely retrieval and the other is about 100k-to-1.)
• Although the SQL standard doesn't mandate data types (and SQLite, for instance, doesn't use them), most datastores will use them because that information can reduce the amount of space taken for a given row. By reducing space, you reduce the seek time for finding records. For example, if you know a column is INT(7), that means it's less than 2^24. That's 3 bytes storing it as an integer, instead of 7 as a string. (And up to 8, if you store it as a null-terminated string.) Over a million rows, that can save 4 megabytes. In addition, if you use an integer there, you can justify using fixed-length rows, which trades space (you potentially use more per row) for speed (you know exactly how wide each row is, so you only have one seek vs. potentially thousands in a naive implementation.)
• A really neat fact about datastores is that optimizing for space actually ends up optimizing for speed, in the general case. This is because datastores are almost completely I/O-bound. The less you have to use the hard-drive, the less time you take. Also - CPU's are improving much much faster than hard-drives. (At least, over platter-based hard-drives.) So, using some form of data compression on a per-row may make sense. (And, some datastores like Oracle and MySQL do offer this option for retrieve-only tables, but that system can be easily improved upon.)
• A concern you have to take into account is the block size. This is the smallest chunk of physical space the operating system will allocate for storage. In most OS'es, this is either 512bytes or 1024bytes. In keeping with the desire to reduce the amount of seek time, you should be aware of this number.

Your questions are actually the same question, from different directions. There is a better way of structuring the data, and that's by keeping the metadata around. Basically, the most important piece of metadata you want is the rowsize. That tells you where the data is that you're looking for. You also want to keep some set of indices which allow you to quickly determine which row numbers have what data in what column. As for storing this metadata ... most datastores keep a separate storage area for this. Oracle keeps it as part of the data file and MySQL keeps it as a separate file (at least for MyISAM tables).

Column types, sizes, referential integrity ... that's all used to aid the developer. SQLite is a good example of a datastore that doesn't make any use of that stuff. (Well, very, very little use.) Column types and sizes can also help the datastore in some optimizations when calculating rowsize. (q.v. above as to why this is important.)

Oh - if you want any sort of decent performance, you will end up rewriting this in C.

This won't help in the actual implementation, but look for stuff written by Fabian Pascal on some theory behind efficient data storage, especially with the relational model. He has a lot of ... good ... articles on the web.

Updates:Wording changes on the sidenote about compression on a per-row basis. Added an example of savings when using data types.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.