I find myself having to deal with lookup tables to preserve space and speed up queries. Something like: create table test ( somedate DATE, long_column_id INT UNSIGNED, ); create table longcol_lookup ( long_column_id_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, long_column CHAR(128) ); If I have a table with 10 million records but only 1000 distinct values for long_column I am wasting lots of space and queries take a long time without the lookup table. If I create the lookup table, queries are more complicated (have to join tables), in addition, inserts and updates are more difficult because now I have to do a lookup and if the lookup fails, I have to insert a new record into the lookup table before I can insert into the main table. Thats a lot of application logic that could be avoided if the dbms transparently handled the lookup stuff in its engine. So the idea is to make such lookup tables transparent. That is, the dbms engine will create them on the fly. If the dbms detects lots of repeating values and average datasize > say 8 bytes, then it will make that column an int or long which will be used as a pointer into a datastore of values. Perhaps the initial conversion to this internal lookup table can be done when OPTIMIZE TABLE is run? The fact that mysql has created an internal lookup table should be hidden from the user. The user doesn't have direct access to the lookup table, and everything works the same as it does now. What do you think of the idea? Has anyone ever heard of a dbms that already does something like that? thanks, -joe --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
