Re: Mysql server - which filesystem to choose? Is it really that important nowadays?

2013-05-23 Thread Manuel Arostegui
2013/5/22 Tim Callaghan tmcallag...@gmail.com Rafal, I don't believe TRIM is supported for XFS. I tried this two weeks ago and worked pretty well: http://xfs.org/index.php/FITRIM/discard Manuel.

Re: Mysql server - which filesystem to choose? Is it really that important nowadays?

2013-05-23 Thread Johan De Meersman
- Original Message - From: Rick James rja...@yahoo-inc.com Subject: RE: Mysql server - which filesystem to choose? Is it really that important nowadays? ext does less well with simultaneous IOPs than xfs. Possibly, but how much less (and which ext)? Without numbers that's not very

Re: Mysql server - which filesystem to choose? Is it really that important nowadays?

2013-05-23 Thread Tim Callaghan
Thanks for the information, I'll give it a try myself. On Thu, May 23, 2013 at 5:02 AM, Manuel Arostegui man...@tuenti.com wrote: 2013/5/22 Tim Callaghan tmcallag...@gmail.com Rafal, I don't believe TRIM is supported for XFS. I tried this two weeks ago and worked pretty well:

Re: Bug in BETWEEN same DATETIME

2013-05-23 Thread Andrew Moore
Sorry, that was meant to be; WHERE (new column stored as date) = '2013-04-16' On Thu, May 23, 2013 at 10:16 PM, Andrew Moore eroomy...@gmail.com wrote: Personally I don't share your view that it's a bug. Omitting the time results in midnight by default so this screws between because there's

RE: Bug in BETWEEN same DATETIME

2013-05-23 Thread Rick James
I use this; it keeps me out of trouble whether I am using * MySQL's DATE vs DATETIME vs TIMESTAMP * Sybase dates (to minute or to millisecond, hence :59:59 does not work) * leap year WHERE dt = ? AND dt ? + INTERVAL ? DAY I fill in the first two ? with the same starting date.

RE: Bug in BETWEEN same DATETIME

2013-05-23 Thread Rick James
Watch out for CAST(), DATE(), and any other function. In a WHERE clause, if you hide an indexed column inside a function, the index cannot be used for optimization. INDEX(datetime_col) ... WHERE DATE(datetime_col) = '2013-01-01' will not use the index! The workaround is messy, but worth

Re: Bug in BETWEEN same DATETIME

2013-05-23 Thread shawn green
On 5/23/2013 4:55 PM, Daevid Vincent wrote: I just noticed what I consider to be a bug; and related, has this been fixed in later versions of MySQL? We are using: mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2 If you use BETWEEN and the same date for both parts

Re: Bug in BETWEEN same DATETIME

2013-05-23 Thread Michael Dykman
where cast(transaction_date as date) BETWEEN '2013-04-16' AND This approach might be problematic in that it requires that every row in the source table be examined so that it's transaction_date can be casted. The original formulation is more efficient as it allows an index on

RE: Bug in BETWEEN same DATETIME

2013-05-23 Thread Peterson, Timothy R
You probably want where cast(transaction_date as date) BETWEEN '2013-04-16' AND '2013-04-16' That works on my test case You could also change the where clause to be = date and date+1 -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Thursday, May 23,

Re: Bug in BETWEEN same DATETIME

2013-05-23 Thread Andrew Moore
Personally I don't share your view that it's a bug. Omitting the time results in midnight by default so this screws between because there's no time between 00:00:00 and 00:00:00. Are you having operational issues here or are you simply fishing for bugs? WHERE `transaction_date` = DATE(datetime)