Re: does the number of column affect performance
what if I have to select all columns every time? any difference in performance? On 2012/02/29, at 4:41, Paul DuBois wrote: On Feb 28, 2012, at 9:59 AM, Zheng Li wrote: for example there are 2 tables to save same data table A has 10 columns: a primary key column and 9 blob column table B has 2 columns : a primary key column and 1 blob column which includes all data in 2nd~10th columns of table A are there any differences in performance when selecting, inserting, updating, and deleting data. Sure. For example, with table A, you can select only those blob columns you're interested in. With B, you have to select all of them if you want *any* of them. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
sorry for the duplicate message. just ignore it. On 2012/03/09, at 10:11, Zheng Li wrote: what if I have to select all columns every time? any difference in performance? On 2012/02/29, at 4:41, Paul DuBois wrote: On Feb 28, 2012, at 9:59 AM, Zheng Li wrote: for example there are 2 tables to save same data table A has 10 columns: a primary key column and 9 blob column table B has 2 columns : a primary key column and 1 blob column which includes all data in 2nd~10th columns of table A are there any differences in performance when selecting, inserting, updating, and deleting data. Sure. For example, with table A, you can select only those blob columns you're interested in. With B, you have to select all of them if you want *any* of them. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
- Original Message - From: Baron Schwartz ba...@xaprb.com You may be interested in this: http://www.mysqlperformanceblog.com/2009/09/28/how-number-of-columns-affects-performance/ Heeh, very interesting. You guys keep churning out the good stuff :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
You may be interested in this: http://www.mysqlperformanceblog.com/2009/09/28/how-number-of-columns-affects-performance/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
On Feb 28, 2012, at 9:59 AM, Zheng Li wrote: for example there are 2 tables to save same data table A has 10 columns: a primary key column and 9 blob column table B has 2 columns : a primary key column and 1 blob column which includes all data in 2nd~10th columns of table A are there any differences in performance when selecting, inserting, updating, and deleting data. Sure. For example, with table A, you can select only those blob columns you're interested in. With B, you have to select all of them if you want *any* of them. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
not to mention, updating any field on table 2 requires the entire contents to be written out again instead of being able to effect those columns individually. Quite a few NoSL solutions work on a similar model which, while useful in places, is decidedly NOT relational. If you are *certain* that the primary key is the only key you will ever need and it is acceptable to read/write all fields together each and every time, then perhaps one of those products will suit you. MySQL is a Relational Database Management System and best suited for relational database management. Don't take this as a specific recommendation. My experience with NoSQL systems suggests that in many cases the application would have been better off with a relational engine underneath. All too often, a lack of up-front analysis lures developers and architects into thinking that the relational properties are not important, only to find out later in the project that they are critical., - michael dykman On Tue, Feb 28, 2012 at 2:41 PM, Paul DuBois paul.dub...@oracle.com wrote: On Feb 28, 2012, at 9:59 AM, Zheng Li wrote: for example there are 2 tables to save same data table A has 10 columns: a primary key column and 9 blob column table B has 2 columns : a primary key column and 1 blob column which includes all data in 2nd~10th columns of table A are there any differences in performance when selecting, inserting, updating, and deleting data. Sure. For example, with table A, you can select only those blob columns you're interested in. With B, you have to select all of them if you want *any* of them. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
even if I select all columns every time no matter it is Table A or B, A has better performance? On 2012/02/29, at 4:41, Paul DuBois wrote: On Feb 28, 2012, at 9:59 AM, Zheng Li wrote: for example there are 2 tables to save same data table A has 10 columns: a primary key column and 9 blob column table B has 2 columns : a primary key column and 1 blob column which includes all data in 2nd~10th columns of table A are there any differences in performance when selecting, inserting, updating, and deleting data. Sure. For example, with table A, you can select only those blob columns you're interested in. With B, you have to select all of them if you want *any* of them. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
