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" <brent@stripped>
>
>> 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/mysql?unsub=1
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
