RE: RE: what is the proper way to store timezone information?
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2008 6:23 AM To: mysql@lists.mysql.com Subject: RE: what is the proper way to store timezone information? Boyd, Todd M. wrote: When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. My concern is whether the time_zone_id is a fixed reference of the timezone. If the id might (for whatever reason) change in the future, I'd have to store the timezone name. http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html You can convert back and forth using the system's time zone table. Read the MySQL manual I've linked to above for more information. Apparently, you can even reference them by offset from UTC (i.e., -6:00 for US Central). The article warns against using the time zone's text description, but I saw nothing about dangers of offsets or time_zone_id. Todd Boyd Web Programmer
RE: Function Still Not Working
-Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 1:40 PM To: Martijn Tonies; MySQL List Subject: Re: Function Still Not Working Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. I think what he means is... in one instance of the function, the data is collated as latin-iso-blahblah, perhaps, and a different collation (one without case sensitivity, eh?) in the other table... As I don't deal with letters/characters outside of the 'standard' Latin-iso-asdfasdf collation, I'm afraid there's not much else I can explain using my limited knowledge. Hopefully, though, that helped to give you an idea of what he was driving at. ;) Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: natural sort via substrings
-Original Message- From: Emily Heureux [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 8:25 PM To: mysql@lists.mysql.com Subject: natural sort via substrings Hi, I am attempting to do a natural sort from within mysql, if possible. So, for example, jane2 would come before jane10, and normal strings would still sort as expected. I found some solutions, like using length for the numerical part, but that only works if the strings are the same length. Ideally, I would like to use substring_index, but stick a regexp in as the delimiter. So far, it seems you cannot do this. Does anyone know how to put a regexp as the delimiter in substring_index? For example, I want to do something like this: .order by substring_index(name, 'regexp [0-9]+', 1); Is this possible? Forgive me if I am incorrect, but wouldn't jane2 already be listed before jane10 if you just ORDER BY fieldname ASC? I suppose jane2 and jane20 would wind up next to each other if this were the case, but can you not zero-fill your values (i.e., jane02) to prevent this from happening? Sorry if my suggestion falls short of the mark, but the conditions for your test case were vague at best. :) Can you not zero-fill? Are you sorting by the entire field's value, or just a portion of it? Wouldn't substring_index() sort all jane## entries arbitrarily, since your (theoretical) example returns everything to the left of the first match (but not including the match)? Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: what is the proper way to store timezone information?
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 5:17 AM To: mysql@lists.mysql.com Subject: what is the proper way to store timezone information? All, I will be recording timezone information based on user input using the time zone names from mysql.time_zone_name - names like 'America/Los_Angeles'. When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. I'd say if it's easier (or perhaps more efficient, if this is a concern) for you to simply store the time_zone_id value, then do it that way. It's not as if it won't store your data if you store the time_zone_name... it just might be harder to use as a variable later, being text instead of an identifying number. Again... totally a matter of your preference. Todd Boyd Web Programmer
RE: natural sort via substrings
-Original Message- From: Boyd, Todd M. [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:33 AM To: Emily Heureux; mysql@lists.mysql.com Subject: RE: natural sort via substrings ---8--- snip Forgive me if I am incorrect, but wouldn't jane2 already be listed before jane10 if you just ORDER BY fieldname ASC? I suppose jane2 Man, what a morning. After reading what I wrote, it's obvious to me that this should not happen (jane2 coming before jane10, I mean.. 1 2). Maybe sort by length first, then sub-sort by alpha? As far as regex goes, I know of no way to use it inside the MySQL function substring_index(). Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
-Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:48 AM To: [EMAIL PROTECTED] Cc: mos; mysql@lists.mysql.com Subject: Re: Fulltext index -first query slow, subsequent queries fast Hi Is sphinxsearch avialable only on for windows regards anandkl On 6/13/08, Rory McKinley [EMAIL PROTECTED] wrote: mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ ---8--- snip GIYF: http://www.linux.com/feature/118721 I believe the *binaries* are only *pre-compiled* for Windows. Search the Sphinx site for info about compilation, or crack open one of the .tgz (an immediate I'm probably a *nix package flag) releases. I'll bet you can compile it yourself on *nix. (The article I've linked to also talks about using Sphinx in BSD distros.) Hope that helps, Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query counts of a database
-Original Message- From: Elim Qiu [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:56 AM To: mysql@lists.mysql.com Subject: query counts of a database I'm looking for a query that reports the count of each table in the database. the query should not assume the table list of the database. Thanks for any inputs Is SELECT COUNT(*) FROM * too open-ended? BTW... do you want the number of records per table, or number of tables per database?? Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: natural sort via substrings
-Original Message- From: Emily Heureux [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 11:12 AM To: Boyd, Todd M.; mysql@lists.mysql.com Subject: RE: natural sort via substrings Hi, I am sorry for being so vague. The values are not as simple as jane20, jane10, or jane2. There are names like, jane-2, alex 3, alex4, and just 'jane', etc. ORDER BY sorts by either numerical OR string, not both, as far as I can tell. I'll have to think about zero-fill, but I doubt that will work given the lack of standards for the names I am getting (protein names). I need a sort that works like the way a human would sort. As for substring_index(), I was using that because the names I am dealing with often have a distinguishing number at the end, so I would like to just grab all but the number(s), and then grab just the number(s) (So, actually, I am using substring_index() twice). If I can separate out the numbers at the end from the rest of the string with a regexp delimiter, the problem is solved. I just don't know if that can be done from within ORDER BY. ---8--- snip Emily, From an exhaustive search of the web (including MySQL's page and others), it appears that Regular Expression support in MySQL is limited at best. All you can do with it thus far is determine whether or not something matched the given expression--1 or 0 are the only results possible, from what I can gather. You may be forced to either nest a bunch of queries using substring_index() and other server-side string manipulation functions, or sort the data after queried-extraction. :( By all means, though, do not take my information as gospel. There may very well be a way to pull off what you're trying to do... but ORDER BY REGEXP is not it. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: natural sort via substrings
From: Jim Lyons [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 12:10 PM To: Boyd, Todd M. Subject: Re: natural sort via substrings What I would do is form 2 additional fields from the first, an alphanumeric field and a numeric field so: jane-2 jane- 2 alex 3 alex 3 (maybe put a blank after the 'x' but be sure to handle it properly, trailing spaces are tricky) alex4 alex 4 jane jane (maybe null, or 0 for the numeric field, depending on the application) Then sort on the 2 fields. Depending on your application, you might want to keep the entire name field as well as the 2 derivative fields, just accept the de-normalization. You can use the entire field for display purposes. If your table is of any size whatsoever this would be vastly preferable to doing all sorts of substring-ing in your where clauses. You won't have a prayer of having the optimizer use an index. Jim On Fri, Jun 13, 2008 at 11:58 AM, Boyd, Todd M. [EMAIL PROTECTED] wrote: -Original Message- From: Emily Heureux [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 11:12 AM To: Boyd, Todd M.; mysql@lists.mysql.com Subject: RE: natural sort via substrings Hi, I am sorry for being so vague. The values are not as simple as jane20, jane10, or jane2. There are names like, jane-2, alex 3, alex4, and just 'jane', etc. ORDER BY sorts by either numerical OR string, not both, as far as I can tell. I'll have to think about zero-fill, but I doubt that will work given the lack of standards for the names I am getting (protein names). I need a sort that works like the way a human would sort. As for substring_index(), I was using that because the names I am dealing with often have a distinguishing number at the end, so I would like to just grab all but the number(s), and then grab just the number(s) (So, actually, I am using substring_index() twice). If I can separate out the numbers at the end from the rest of the string with a regexp delimiter, the problem is solved. I just don't know if that can be done from within ORDER BY. ---8--- snip Emily, From an exhaustive search of the web (including MySQL's page and others), it appears that Regular Expression support in MySQL is limited at best. All you can do with it thus far is determine whether or not something matched the given expression--1 or 0 are the only results possible, from what I can gather. You may be forced to either nest a bunch of queries using substring_index() and other server-side string manipulation functions, or sort the data after queried-extraction. :( By all means, though, do not take my information as gospel. There may very well be a way to pull off what you're trying to do... but ORDER BY REGEXP is not it. Jim, Thank you for formulating my idea into a more easily-understandable format. :) It's been a long week. What I meant to say when I mentioned sort the data after queried-extraction was that you would need to split the data extracted with a query into its sort-able parts as you described. Very well put on your part, though. Emily, You might try and find a conditional string function that will find the first digit character in a string (or a nifty manipulation of a different string function that accomplishes the same objective). I understand that's sort of along the line of what you were trying to do in the first place, but perhaps this new funneling of the problem will give you some different insight into a possible solution. I'll scour the web and documentation with the free time I may or may not get today and see what I can come up with, as well. Don't give up hope! :) This sort of data transformation is not an uncommon occurrence, and someone somewhere must have tried to tackle a similar problem--with positive results. *crosses fingers* If not, maybe someone else's idea for a solution can plant the seed. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2008 6:23 AM
To: MySQL List
Subject: Re: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time,
and
I'm
still having the problem. I spent HOURS the other day manually
going
through the data and Properizing these things by hand. I don't want
to do
that again if I can avoid it. If anyone has any clues on this one,
I
would
appreciate it.
---8--- snip
It's a very simple function used to properize a string sent to it.
When I
do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our
server
that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a
problem
with the older version that would cause this function not to work
properly?
Any difference in default collation?
I am curious about that, as well. It brings to mind a discussion that
happened on this list last week (I believe) about case
sensitive/insensitive use of LIKE. I believe the synopsis was that
tables are either created as case-insensitive, or the search needs to be
specified as case sensitive (with BINARY).
Could this be a similar issue, perhaps? One table is specifically
case-insensitive with regard to the function, and the other is not?
Just spit-balling...
Todd Boyd
Web Programmer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enable and disable keys
-Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enable and disable keys
From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 10:27 AM To: Boyd, Todd M. Cc: mysql Subject: Re: enable and disable keys /tmp has 16GB free space On 6/12/08, Boyd, Todd M. [EMAIL PROTECTED] wrote: -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Sorry if this question is stupid, but... okay, your /tmp folder has 16GB available for use. Is this the folder that MySQL has been configured to use? I understand that it should be by default, but it doesn't hurt to examine every facet of a problem with confusing roots. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
