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]
