Re: What should it be in MySql? In C, it's an array of integers.
On Thu, Sep 17, 2009 at 3:46 AM, John Meyer john.l.me...@gmail.com wrote: Alternatively, you can skip the A_ID and have a compound key of USER_ID and A_NUMBER on the ASSOC_NUMBERS table. I prefer the A_ID, though. Note that this would be marginally faster, because all your data is in the index, so you don't need to do an additional lookup into the data segment of your table. -- That which does not kill you was simply not permitted to do so for the purposes of the plot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What should it be in MySql? In C, it's an array of integers.
Johan De Meersman wrote: On Thu, Sep 17, 2009 at 3:46 AM, John Meyer john.l.me...@gmail.com wrote: Alternatively, you can skip the A_ID and have a compound key of USER_ID and A_NUMBER on the ASSOC_NUMBERS table. I prefer the A_ID, though. Note that this would be marginally faster, because all your data is in the index, so you don't need to do an additional lookup into the data segment of your table. And assuming that the numbers don't duplicate in your array. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What should it be in MySql? In C, it's an array of integers.
Hi folks -- What would be the right approach in MySql 5.0? My table, USERS, has columns NAME and IP. Associated with each user is also a collection of from 0 to 50 INTs. What's a reasonable way to put these 50 INTs in the table without using 50 separate columns, INT01...INT50? Is BLOB an OK approach? I have to manipulate these INTs in my CGI code. Thanks! -- Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What should it be in MySql? In C, it's an array of integers.
Pete Wilson wrote: Hi folks -- What would be the right approach in MySql 5.0? My table, USERS, has columns NAME and IP. Associated with each user is also a collection of from 0 to 50 INTs. What's a reasonable way to put these 50 INTs in the table without using 50 separate columns, INT01...INT50? Is BLOB an OK approach? I have to manipulate these INTs in my CGI code. Thanks! -- Pete Break them out into a separate table linked via the primary key. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What should it be in MySql? In C, it's an array of integers.
Be careful about burying list type of data in a column. I've seen poor performance issues parsing lists and XML type data buried in columns. A lot depends on your application and how and what you need to query from those lists. I've seen a case where a submitted documents were stored in a column as an XML doc where the application had to fetch all the submissions within the group and parse them in order to figure out who the submitters were. This was being done at the gateway page of that tool. It was a performance nightmare. An alternative might be to store the integers vertically in a table with a column describing the position of the value within the list. Kinda off the cuff but something to think about. Kyong On Wed, Sep 16, 2009 at 5:29 PM, John Meyer john.l.me...@gmail.com wrote: Pete Wilson wrote: Hi folks -- What would be the right approach in MySql 5.0? My table, USERS, has columns NAME and IP. Associated with each user is also a collection of from 0 to 50 INTs. What's a reasonable way to put these 50 INTs in the table without using 50 separate columns, INT01...INT50? Is BLOB an OK approach? I have to manipulate these INTs in my CGI code. Thanks! -- Pete Break them out into a separate table linked via the primary key. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=kykim...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What should it be in MySql? In C, it's an array of integers.
Pete wrote: Hi folks -- What would be the right approach in MySql 5.0? My table, USERS, has columns NAME and IP. Associated with each user is also a collection of from 0 to 50 INTs. What's a reasonable way to put these 50 INTs in the table without using 50 separate columns, INT01...INT50? Is BLOB an OK approach? I have to manipulate these INTs in my CGI code. Thanks! -- Pete Break them out into a separate table linked via the primary key. How elegant! Thanks. -- Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What should it be in MySql? In C, it's an array of integers.
Pete Wilson wrote: Break them out into a separate table linked via the primary key. How elegant! Thanks. -- Pete it's nothing not taught in Database Design 101. Typically you would have a setup like this USERS USER_ID --primary key USER_NAME USER_IP ASSOC_NUMBERS A_ID --primary key USER_ID -- foreign key linked to users A_NUMBER ---one of the integers that you would store Alternatively, you can skip the A_ID and have a compound key of USER_ID and A_NUMBER on the ASSOC_NUMBERS table. I prefer the A_ID, though. Another way I've read about (though I can't remember the article) is that certain types of databases do allow you to store an array. These are normally used for databases that need to be very, very small (like on an embedded chip). However, the way I described is usually the default for relational databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
