Is the date really stored as an integer? You say so, but then you're
trying a wildcard (i.e. char) search. If so, try this (pretending
date_int is your date stored as an integer column in the table
large_table):
SELECT * FROM large_table WHERE date_int>=0600000 AND date_int<0700000;
to retrieve all rows in June. That should be relatively fast, if the date
column is indexed.
If the date is really a string, then I suppose you could use
SELECT * FROM large_table WHERE date_int LIKE '06%';
That should still be fairly fast, if the date column is indexed.
I would imagine, however, that you'll need other sorts of partial
searches. By year, say. Or decade.... In that case, you should really
consider massaging your data into a better format. I'd suggest converting
your date column to datetime type, and add a month column for by-month
searches. If you'll be doing by-day searches (What happened on the 3rd of
every month for the last 200 years?), then you may want a day column, as
well. No need for a year column, as year range selects with a datetime
column should be pretty efficient. Of course, you'll probably want an
index on both the datetime and the month columns to avoid the full table
scans.
Michael
On Mon, 10 Dec 2001, jeff wrote:
> I inherited a large table (50 million records) with the date stored as an
> integer in the format (mmddyyy). The dates go back to the 1800's and in the
> 1990's so the year is either 8xx or 9xx.
>
> I need to be able to search by partial dates. For example, some searches
> need to pull every record for the month of June. What is the best way to
> search? Do I need to separate the date into different columns (day, month,
> year)? I have tried wildcard searches, but they seem to do an entire table
> scan and are very slow.
>
> Thanks,
> Jeff
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
