Do NULL values slow down the database?
Hi, I've read reacently, that it's not good to use columns with NULL values in MySQL, because it slows down the select queries over that columns. Is it true? Or do that affects only some situations or some versions? Are there some relevant statistics about that? Thanks a lot, Ales On 3/29/07, Reinhart Viane [EMAIL PROTECTED] wrote: Hello list, I have a table events in a database that has a field named duration. This field is a mediumint containing an amount of minutes (eg 65, 87, 10368) Now I need these to be outputted into a h:mm (so 65 will be represented as 1:05) My complete query is: select YEAR(events.workdate) as theyear, (sum(events.duration)/60),clients.name, persons.name from events, persons, clients where events.personid= persons.personid and events.clientid= clients.clientid group by clients.name, events.personid, theyear; this does, off course not give me the wanted result. How can I convert these numerical entries to hh:mm in my query? (days do not matter, I just need hours and minutes, thx) Regards and thanks, Reinhart Viane D-studio Graaf van Egmontstraat 15/3 2800 Mechelen [EMAIL PROTECTED] +32(0)15 44 89 01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- Ales Zoulek NetCentrum s.r.o. +420 739 542 789 +420 604 332 515 ICQ: 82647256 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do NULL values slow down the database?
Ales Zoulek wrote: Hi, I've read reacently, that it's not good to use columns with NULL values in MySQL, because it slows down the select queries over that columns. Is it true? Or do that affects only some situations or some versions? Are there some relevant statistics about that? There is not really a noticeable slowdown just for having NULLable columns. However, there are situations where separating off frequently accessed columns from (often NULLable) infrequently accessed columns into two or more tables can provide very good performance improvement, as the infrequently accessed columns have much less likelihood from taking up space in memory, especially in memory-starved applications. Cheers, Jay On 3/29/07, Reinhart Viane [EMAIL PROTECTED] wrote: Hello list, I have a table events in a database that has a field named duration. This field is a mediumint containing an amount of minutes (eg 65, 87, 10368) Now I need these to be outputted into a h:mm (so 65 will be represented as 1:05) My complete query is: select YEAR(events.workdate) as theyear, (sum(events.duration)/60),clients.name, persons.name from events, persons, clients where events.personid= persons.personid and events.clientid= clients.clientid group by clients.name, events.personid, theyear; this does, off course not give me the wanted result. How can I convert these numerical entries to hh:mm in my query? (days do not matter, I just need hours and minutes, thx) Regards and thanks, Reinhart Viane D-studio Graaf van Egmontstraat 15/3 2800 Mechelen [EMAIL PROTECTED] +32(0)15 44 89 01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do NULL values slow down the database?
Thanks a lot. I agree with you in meaningful separating columns into tables as you descibed it. I had just impression, that mysql have some inner algoritmic issue with dealing with NULL values. Rgrds. Ales On 3/29/07, Jay Pipes [EMAIL PROTECTED] wrote: Ales Zoulek wrote: Hi, I've read reacently, that it's not good to use columns with NULL values in MySQL, because it slows down the select queries over that columns. Is it true? Or do that affects only some situations or some versions? Are there some relevant statistics about that? There is not really a noticeable slowdown just for having NULLable columns. However, there are situations where separating off frequently accessed columns from (often NULLable) infrequently accessed columns into two or more tables can provide very good performance improvement, as the infrequently accessed columns have much less likelihood from taking up space in memory, especially in memory-starved applications. Cheers, Jay On 3/29/07, Reinhart Viane [EMAIL PROTECTED] wrote: Hello list, I have a table events in a database that has a field named duration. This field is a mediumint containing an amount of minutes (eg 65, 87, 10368) Now I need these to be outputted into a h:mm (so 65 will be represented as 1:05) My complete query is: select YEAR(events.workdate) as theyear, (sum(events.duration)/60),clients.name, persons.name from events, persons, clients where events.personid= persons.personid and events.clientid= clients.clientid group by clients.name, events.personid, theyear; this does, off course not give me the wanted result. How can I convert these numerical entries to hh:mm in my query? (days do not matter, I just need hours and minutes, thx) Regards and thanks, Reinhart Viane D-studio Graaf van Egmontstraat 15/3 2800 Mechelen [EMAIL PROTECTED] +32(0)15 44 89 01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- -- Ales Zoulek NetCentrum s.r.o. +420 739 542 789 +420 604 332 515 ICQ: 82647256 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do NULL values slow down the database?
So Jay, What benefit do we get if we only null columns in a separate table, since they are null, then there is not point in access this table. Please correct me , i am bit confused by this. regards anandkl On 3/30/07, Ales Zoulek [EMAIL PROTECTED] wrote: Thanks a lot. I agree with you in meaningful separating columns into tables as you descibed it. I had just impression, that mysql have some inner algoritmic issue with dealing with NULL values. Rgrds. Ales On 3/29/07, Jay Pipes [EMAIL PROTECTED] wrote: Ales Zoulek wrote: Hi, I've read reacently, that it's not good to use columns with NULL values in MySQL, because it slows down the select queries over that columns. Is it true? Or do that affects only some situations or some versions? Are there some relevant statistics about that? There is not really a noticeable slowdown just for having NULLable columns. However, there are situations where separating off frequently accessed columns from (often NULLable) infrequently accessed columns into two or more tables can provide very good performance improvement, as the infrequently accessed columns have much less likelihood from taking up space in memory, especially in memory-starved applications. Cheers, Jay On 3/29/07, Reinhart Viane [EMAIL PROTECTED] wrote: Hello list, I have a table events in a database that has a field named duration. This field is a mediumint containing an amount of minutes (eg 65, 87, 10368) Now I need these to be outputted into a h:mm (so 65 will be represented as 1:05) My complete query is: select YEAR(events.workdate) as theyear, (sum(events.duration)/60),clients.name, persons.name from events, persons, clients where events.personid= persons.personid and events.clientid= clients.clientid group by clients.name, events.personid, theyear; this does, off course not give me the wanted result. How can I convert these numerical entries to hh:mm in my query? (days do not matter, I just need hours and minutes, thx) Regards and thanks, Reinhart Viane D-studio Graaf van Egmontstraat 15/3 2800 Mechelen [EMAIL PROTECTED] +32(0)15 44 89 01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- -- Ales Zoulek NetCentrum s.r.o. +420 739 542 789 +420 604 332 515 ICQ: 82647256 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
