Re: efficient query for: "it's your birthday today"
NB! ...WHERE doycol=312
Valid only for dates <= 28.Feb
OR Dates > 29.Feb but not in leap year(s) ;-)
> doycol=DAYOFYEAR("2004-11-07")
>
> or
>
> doycol=312
Eric McGrane wrote:
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be for
11/7 would be
doycol=DAYOFYEAR("2004-11-07")
or
doycol=312
E
""Jigal van Hemert"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
I have date of birth stored in a DATETIME column and need to find for
which
persons a certain date is their birthday.
I've tried so far:
DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec
col LIKE '%-11-07%'=> 0.1643 sec
col RLIKE '-11-07'=> 0.1702 sec
Are there faster alternatives for MySQL 4.0.21 ?
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
From: "Brent Baisley" > You don't have to replicate the data, and you shouldn't. Since you will > be searching on parts of the data, you may want to store it in parts. > Then you can create indexes to "merge" the data for searching. I'll be searching both on parts of the date as well as on the entire date. Queries like "col > CURDATE() - INTERVAL 2 DAY" are also used; I don't see how that is going to be fast if you store the date parts in seperate columns. > Now, if your table is only 10K records, that's pretty small and you > probably won't see any performance difference using indexes since the > table may be in cache. If the table is not going to get a lot bigger > (i.e. 50K+ records), it may be easier just to make sure you have plenty > of RAM in the machine. The test machine currently has only 10K records, but we're designing the rest of the application for 500K+ records... That's why I wanted to have faster queries for this... The actual lay-out of the database is a bit more complicated than the queries may have indicated. Since we don't know yet the actual data that is going to be stored we decided that the database would best be storing each type of data in a table. So we have tables for int, bigint, float, string, text and datetime data (more tables can be added without a problem). CREATE TABLE `param_datetime` ( `id` int(11) NOT NULL default '0', `name` varchar(32) NOT NULL default '', `value` datetime default NULL, `match` datetime default NULL, `match2` datetime default NULL, `weight` tinyint(4) default NULL, KEY `id-name-value-match-match2` (`id`,`name`,`value`,`match`,`match2`) ) TYPE=InnoDB COMMENT='integer parameters'; `id` links to the id to which the data belongs `name` is the name of the field `value` contains the actual data `match` and `match2` are used for storing targets, values to look for, etc. `weight` is used for calculating the sort order We've tested models for flat tables (a column for each parameter) and these parameter tables, both as MyISAM and InnoDB. For high concurrency situations the InnoDB tables outperformed MyISAM easily for larger tables. Parameter tables were easier to expand compared to 'flat' tables: adding a column to a 500K+ record table is not fast. Indexing is also more efficient with parameter tables in our case, since any combination of properties may be used for searching. We have one limitation however: you cannot search on more that 31 properties at the same time, due to the limitation of the number of JOINs in MySQL. We're currently running queries with up to 27 JOINs, but since they all use an index, performance has been very constant. Clever caching of results is also helping a lot to keep the load of the database server down to a minimum. I'll start to experiment with adding the date parts to the param_datetime table and adding some indexes for these parts. Maybe this will speed this up considerably... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
Store month*100+day
1999-03-01 --> 301
you can also say "it's your birthday today" when 02/29 doesn't exists!
Santino
At 13:05 -0500 18-11-2004, [EMAIL PROTECTED] wrote:
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQL>select dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-+-+
| 60 | 61 |
+-+-+
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eric McGrane" <[EMAIL PROTECTED]> wrote on 11/18/2004 11:29:20 AM:
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be
for
11/7 would be
doycol=DAYOFYEAR("2004-11-07")
or
doycol=312
E
""Jigal van Hemert"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I have date of birth stored in a DATETIME column and need to find for
which
> persons a certain date is their birthday.
>
> I've tried so far:
>
> DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec
>
> col LIKE '%-11-07%'=> 0.1643 sec
>
> col RLIKE '-11-07'=> 0.1702 sec
>
> Are there faster alternatives for MySQL 4.0.21 ?
>
> Regards, Jigal.
>
--
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: efficient query for: "it's your birthday today"
Yup, very good point. I am the one who is sorry. My bad.
E
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 1:06 PM
To: McGrane, Eric
Cc: [EMAIL PROTECTED]
Subject: Re: efficient query for: "it's your birthday today"
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQL>select dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-+-+
| 60 | 61 |
+-+-+
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eric McGrane" <[EMAIL PROTECTED]> wrote on 11/18/2004 11:29:20 AM:
> How about adding another column that stores the day of year for the
> birthday. You could then index on this column and your query would be
for
> 11/7 would be
>
> doycol=DAYOFYEAR("2004-11-07")
>
> or
>
> doycol=312
>
> E
> ""Jigal van Hemert"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > I have date of birth stored in a DATETIME column and need to find
for
> which
> > persons a certain date is their birthday.
> >
> > I've tried so far:
> >
> > DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec
> >
> > col LIKE '%-11-07%'=> 0.1643 sec
> >
> > col RLIKE '-11-07'=> 0.1702 sec
> >
> > Are there faster alternatives for MySQL 4.0.21 ?
> >
> > Regards, Jigal.
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
Re: efficient query for: "it's your birthday today"
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQL>select dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-+-+
| 60 | 61 |
+-+-+
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eric McGrane" <[EMAIL PROTECTED]> wrote on 11/18/2004 11:29:20 AM:
> How about adding another column that stores the day of year for the
> birthday. You could then index on this column and your query would be
for
> 11/7 would be
>
> doycol=DAYOFYEAR("2004-11-07")
>
> or
>
> doycol=312
>
> E
> ""Jigal van Hemert"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > I have date of birth stored in a DATETIME column and need to find for
> which
> > persons a certain date is their birthday.
> >
> > I've tried so far:
> >
> > DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec
> >
> > col LIKE '%-11-07%'=> 0.1643 sec
> >
> > col RLIKE '-11-07'=> 0.1702 sec
> >
> > Are there faster alternatives for MySQL 4.0.21 ?
> >
> > Regards, Jigal.
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
Re: efficient query for: "it's your birthday today"
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be for
11/7 would be
doycol=DAYOFYEAR("2004-11-07")
or
doycol=312
E
""Jigal van Hemert"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I have date of birth stored in a DATETIME column and need to find for
which
> persons a certain date is their birthday.
>
> I've tried so far:
>
> DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec
>
> col LIKE '%-11-07%'=> 0.1643 sec
>
> col RLIKE '-11-07'=> 0.1702 sec
>
> Are there faster alternatives for MySQL 4.0.21 ?
>
> Regards, Jigal.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
You don't have to replicate the data, and you shouldn't. Since you will be searching on parts of the data, you may want to store it in parts. Then you can create indexes to "merge" the data for searching. For instance, you use three fields for storage: year, month, day. The you can create two compound indexes, one on year+month+day and one on just month+day. Searching will then be very fast, regardless of whether you are searching on the full "date" or just the month and day. Now, if your table is only 10K records, that's pretty small and you probably won't see any performance difference using indexes since the table may be in cache. If the table is not going to get a lot bigger (i.e. 50K+ records), it may be easier just to make sure you have plenty of RAM in the machine. On Nov 18, 2004, at 11:14 AM, Jigal van Hemert wrote: From: "Brent Baisley" <[EMAIL PROTECTED]> Without breaking the "date" up into it's separate parts, you can't use an index, so you will always do a full table scan. Your searches will get slower as you add more records. That was what I feared; I was just hoping that MySQL wouldn't treat the DATETIME column type as a variation of a string or an integer (with a set of functions to extract various parts of the datetime), but as a type with a special kind of indexing, etc. Searching for month + date or other parts of a datetime is pretty common and it would be useful to be able to do these kind of operations without storing the same data in more than one place. I don't know how many records you are searching on in your example, but if you have a lot, the difference is pretty minimal and may be due to slightly different loads on the computer. Although the DATE_FORMAT one has the extra overhead of formating every single record to do the comparison. The tests were made on a slow test server with about 10,000 records (if queries run fast on this server they'll be blazingly fast on the production machines ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
From: "Rhino" <[EMAIL PROTECTED]> > I don't know if it is more efficient but I would write this query as > follows, simply because it is easier to understand what it is doing when you > look at it: > > select [whatever columns you want] > from mytable > where month(birthdate) = 11 > and dayofmonth(birthdate) = 7; Sorry, same speed (approx 0.18 sec). This seems to be the speed at which a full tablescan can be performed on this recordset. I guess we have to make some adjustments before the table can grow to 500,000+ records... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **[SPAM]** Re: efficient query for: "it's your birthday today"
[snip] From: "Jay Blanchard" <[EMAIL PROTECTED]> > So if I index a date field (given -mm-dd) and then > > SELECT `userNames` > FROM `userTable` > WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) > > it is still very fast. I have a table with several thousand records in > it and I get back ... > > 30 rows in set (0.00 sec) on my test machine this variation takes approx. 0.18 seconds... Have you tried: SELECT SQL_NO_CACHE `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) To prevent the use of the query cache? Otherwise you will get very fast results, just because the query is not executed at all, but the result is returned from the cache... [/snip] That first result was a non-cached query on a dual processor server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
From: "Jay Blanchard" <[EMAIL PROTECTED]> > So if I index a date field (given -mm-dd) and then > > SELECT `userNames` > FROM `userTable` > WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) > > it is still very fast. I have a table with several thousand records in > it and I get back ... > > 30 rows in set (0.00 sec) on my test machine this variation takes approx. 0.18 seconds... Have you tried: SELECT SQL_NO_CACHE `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) To prevent the use of the query cache? Otherwise you will get very fast results, just because the query is not executed at all, but the result is returned from the cache... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
From: "Brent Baisley" <[EMAIL PROTECTED]> > Without breaking the "date" up into it's separate parts, you can't use > an index, so you will always do a full table scan. Your searches will > get slower as you add more records. That was what I feared; I was just hoping that MySQL wouldn't treat the DATETIME column type as a variation of a string or an integer (with a set of functions to extract various parts of the datetime), but as a type with a special kind of indexing, etc. Searching for month + date or other parts of a datetime is pretty common and it would be useful to be able to do these kind of operations without storing the same data in more than one place. > I don't know how many records you > are searching on in your example, but if you have a lot, the difference > is pretty minimal and may be due to slightly different loads on the > computer. Although the DATE_FORMAT one has the extra overhead of > formating every single record to do the comparison. The tests were made on a slow test server with about 10,000 records (if queries run fast on this server they'll be blazingly fast on the production machines ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
- Original Message - From: "Jigal van Hemert" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, November 18, 2004 4:28 AM Subject: efficient query for: "it's your birthday today" > I have date of birth stored in a DATETIME column and need to find for which > persons a certain date is their birthday. > > I've tried so far: > > DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec > > col LIKE '%-11-07%'=> 0.1643 sec > > col RLIKE '-11-07'=> 0.1702 sec > > Are there faster alternatives for MySQL 4.0.21 ? > I don't know if it is more efficient but I would write this query as follows, simply because it is easier to understand what it is doing when you look at it: select [whatever columns you want] from mytable where month(birthdate) = 11 and dayofmonth(birthdate) = 7; Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
[snip] Really what you are trying to do is search on month + day, not a date. For special "dates" (birthday, anniversary, etc), I always store the year separately. Especially since some people don't really want you to know how old they are. Without breaking the "date" up into it's separate parts, you can't use an index, so you will always do a full table scan. Your searches will get slower as you add more records. I don't know how many records you are searching on in your example, but if you have a lot, the difference is pretty minimal and may be due to slightly different loads on the computer. Although the DATE_FORMAT one has the extra overhead of formating every single record to do the comparison. [/snip] So if I index a date field (given -mm-dd) and then SELECT `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) it is still very fast. I have a table with several thousand records in it and I get back ... 30 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: "it's your birthday today"
Really what you are trying to do is search on month + day, not a date. For special "dates" (birthday, anniversary, etc), I always store the year separately. Especially since some people don't really want you to know how old they are. Without breaking the "date" up into it's separate parts, you can't use an index, so you will always do a full table scan. Your searches will get slower as you add more records. I don't know how many records you are searching on in your example, but if you have a lot, the difference is pretty minimal and may be due to slightly different loads on the computer. Although the DATE_FORMAT one has the extra overhead of formating every single record to do the comparison. On Nov 18, 2004, at 4:28 AM, Jigal van Hemert wrote: I have date of birth stored in a DATETIME column and need to find for which persons a certain date is their birthday. I've tried so far: DATE_FORMAT (col, '%m%d') = '1107'=> 0.2001 sec col LIKE '%-11-07%'=> 0.1643 sec col RLIKE '-11-07'=> 0.1702 sec Are there faster alternatives for MySQL 4.0.21 ? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
