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]
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: Sorting Problem
When using "USING" clause, yes the column must have the same name. But there is an alternative called "ON" clause. Example usage of "USING": table t1, columns a,b table t2: columns a,c You want match t1.a against t2.a: SELECT t1.a, t1.b, t2.c FROM t1 LEFT JOIN t2 USING(a); - Example usage of "ON": table t1, columns a,b table t2, columns x,y You want match t1.a against t2.x: SELECT t1.a, t1.b, t2.y FROM t1 LEFT JOIN t2 ON(t1.a = t2.x); On 3/26/07, Sid Price <[EMAIL PROTECTED]> wrote: Thank you. You say the column must be in both tables, do you mean that the column name must be the same in each table? Sid. Sid Price Software Design http://www.softtools.com -- *From:* Ales Zoulek [mailto:[EMAIL PROTECTED] *Sent:* Monday, March 26, 2007 10:06 AM *To:* [EMAIL PROTECTED] *Cc:* Micah Stevens; mysql@lists.mysql.com *Subject:* Re: Sorting Problem Try: select * from business_names left join business_entries using (bus_id) left join business_categories using (bcat_id) where business_categories.bcat_id=17 order by business_names.organisation You must have brackets around column name after "USING" and do not write table names there, 'cause the column must be in both tables. A. On 3/26/07, *Sid Price* <[EMAIL PROTECTED]> wrote: Micah, I think I understand, here is my query, however I get a syntax error report and it is not clear what the problem is. Any suggestions: select * from business_names left join business_entries using business_entries.bus_id left join business_categories using business_categories.bcat_id where business_categories.bcat_id=17 order by business_names.organisation Thank you for your help, Sid. Sid Price Software Design http://www.softtools.com _ From: Micah Stevens [mailto:[EMAIL PROTECTED] ] Sent: Monday, March 26, 2007 1:10 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Sorting Problem The query using JOIN syntax (you can read about this in the documentation) describes the interelationship between the three tables you described, in this way you can select information based on a WHERE clause as it relates to the category table, while still ordering by the business table. Read up on joins, that may help. -Micah Sid Price wrote: I am sorry but I don't understand the select query you wrote, could you perhaps explain how it works or point me to a reference that might help me understand it? Many thanks for responding, Sid. Sid Price Software Design http://www.softtools.com -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Sunday, March 25, 2007 9:23 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Sorting Problem This doesn't work? SELECT businesses.name from businesses left join links using (businessID) left join categories using (categoryID) where category.name = 'something' order by businesses.name ASC On 03/25/2007 12:40 PM, Sid Price wrote: Hello, I have a MySQL database design that provides an online business directory. It consists of three tables; one with the names and addresses of the businesses (names), one with the categories of businesses (categories), and one that has an entry (a category ID and a business ID) for each business/category pairing (entries), a business may be in multiple categories. The problem I am having is after having queried the "entries" table for all the entries for a given category I query the "names" table for each entry to display the business name and address, I can not figure a way to sort the displayed data by company name, The "entries" table holds the business ID and category ID so sorting the entries that match the category doesn't help. Do I need to create a temporary table to hold the business names/addresses in a category so that I can then sort it? Thanks, Sid. Sid Price Software Design http://www.softtools.com -- -- Ales Zoulek NetCentrum s.r.o. +420 739 542 789 +420 604 332 515 ICQ: 82647256 ------ -- -- Ales Zoulek NetCentrum s.r.o. +420 739 542 789 +420 604 332 515 ICQ: 82647256 --
Re: Sorting Problem
Try: select * from business_names left join business_entries using (bus_id) left join business_categories using (bcat_id) where business_categories.bcat_id=17 order by business_names.organisation You must have brackets around column name after "USING" and do not write table names there, 'cause the column must be in both tables. A. On 3/26/07, Sid Price <[EMAIL PROTECTED]> wrote: Micah, I think I understand, here is my query, however I get a syntax error report and it is not clear what the problem is. Any suggestions: select * from business_names left join business_entries using business_entries.bus_id left join business_categories using business_categories.bcat_id where business_categories.bcat_id=17 order by business_names.organisation Thank you for your help, Sid. Sid Price Software Design http://www.softtools.com _ From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 1:10 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Sorting Problem The query using JOIN syntax (you can read about this in the documentation) describes the interelationship between the three tables you described, in this way you can select information based on a WHERE clause as it relates to the category table, while still ordering by the business table. Read up on joins, that may help. -Micah Sid Price wrote: I am sorry but I don't understand the select query you wrote, could you perhaps explain how it works or point me to a reference that might help me understand it? Many thanks for responding, Sid. Sid Price Software Design http://www.softtools.com -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Sunday, March 25, 2007 9:23 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Sorting Problem This doesn't work? SELECT businesses.name from businesses left join links using (businessID) left join categories using (categoryID) where category.name = 'something' order by businesses.name ASC On 03/25/2007 12:40 PM, Sid Price wrote: Hello, I have a MySQL database design that provides an online business directory. It consists of three tables; one with the names and addresses of the businesses (names), one with the categories of businesses (categories), and one that has an entry (a category ID and a business ID) for each business/category pairing (entries), a business may be in multiple categories. The problem I am having is after having queried the "entries" table for all the entries for a given category I query the "names" table for each entry to display the business name and address, I can not figure a way to sort the displayed data by company name, The "entries" table holds the business ID and category ID so sorting the entries that match the category doesn't help. Do I need to create a temporary table to hold the business names/addresses in a category so that I can then sort it? Thanks, Sid. Sid Price Software Design http://www.softtools.com -- -- Ales Zoulek NetCentrum s.r.o. +420 739 542 789 +420 604 332 515 ICQ: 82647256 --
Re: Not Sorting Correctly
pls, post result of:
SELECT RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName FROM
Ales
On 3/19/07, Jesse <[EMAIL PROTECTED]> wrote:
I have an app that I've converted to MySQL from MS SQL. I used to use the
following to force a Alpha field to sort as if it were numeric (I know,
perhaps it is better if I made the field numeric to begin with, but it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the first
sort "field", and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
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: Posting A Question
Just like you did this right now :) Ales. On 3/19/07, John Sutton <[EMAIL PROTECTED]> wrote: Hi... How do I go about posting a question about MySQL? Thank you, John Sutton -- 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: Character sets
Hello. In general, You can never be sure that (some time in future) you won't need to store different national characters. For example some non-english surname or whatever. So, If you can easily decide it now, use UTF. It's painless choice now and it can come handy in future. Ales Zoulek On 3/19/07, Olaf Stein <[EMAIL PROTECTED]> wrote: Hi All, What character sets would you recommend for a server in the US and all data stays within the English language. Currently everything is set to latin1 with latin1_swedich_ci collation which seems to be the closets one to what I need from what I have read, I was just wondering if there is a better option (e.g. What about UTF or Unicode) Thanks Olaf -- 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: Safe DB Distribution
On 1/30/07, Dan Nelson <[EMAIL PROTECTED]> wrote: In the last episode (Jan 29), Suhas Pharkute said: > Ok, I will explain it again, > > I need a way so that if some body gets the DB files (.MYD and .MYI) > from my distribution, they can put it in their DB engine and can read > my DB. I would like to STOP this stealing. > > Is there any way? No, I guest there isn't. But I somewhat can't get rid of feeling, that the problem is not in lack of mysql features, but in your software analysis. Rgrds. Ales -- ------ Ales Zoulek +420 604 332 515 ICQ: 82647256 http://www.al3x.cz/ --
Re: Safe DB Distribution
What is it exactly that you need to achieve? PHP script doesnt read a MySQL data files, MySQL server does. You can configure your OS and filesystem rights, so that only MySQL server can read them. Ales. -- Forwarded message -- From: "Suhas Pharkute" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Date: Mon, 29 Jan 2007 11:48:27 -0700 Subject: Safe DB Distribution HI, I want to distribute the MySQL DB to different user with an PHP web app. My biggest problem is MySQL data files. Is there anyway by which I can configure the MySQL so that only PHP script can read it and if you happen to copy the Data files and try to use it, that will be useless. I know, this is been in discussion before, but if I can get any other idea to it, I would appreciate it Thank you in advance, Suhas -- ------ Ales Zoulek +420 604 332 515 ICQ: 82647256 http://www.al3x.cz/ -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
