>mysql ... which would prevent index usage. If your kind of query is
> frequent and slow, you could split your DATETIME column into two
> separate columns for DATE and TIME and put an index onto TIME.
Ok, thanks for the help!
Regards Emil
--
Emil Rasmussen
http://noget.net
-
>
> It sounds to me as if you need to change your table design. You want to
> search by hours only,
> so you should have an hours column in your database. Either you should
> break the date up into
> DATE and TIME fields
I have been thinking on doing that, but there was something about it, that i
Emil,
You might try to split the time stamp into separate date and time columns. That would
allow you to cretae and use an index on time alone.
At 14:24 2001-12-05 +0100, Emil Rasmussen wrote:
>>
>> WHERE myfield LIKE 'HH'
>>
>> However this query will probably not use your index
> WHERE myfield LIKE 'HH'
>
> However this query will probably not use your index either :)
Yeah, it works, but still, it does not use my index as long as it is
working
with strings. But neat trick anyway.
It sounds to me as if you need to change your table design. You want to
sea
> If your field is a standard MySQL DATE/TIME field then
>
> HOUR(myfield) will extract the hour portion from myfield.
>
> Thus you can have
>
> WHERE HOUR(myfield) = 14
Yes, that is what i am doing now, but that is really slow, because all the
dates, have to converted to strings, before MySQL
> You have to include the full format to make it work, i.e. including the
> MMDD section of the MMDDHHMMSS format. The above query will
probably
> yield no results because you have no data in the given range (year ).
Yes i know, but i want the data from all years, months and days sort
On Wednesday 05 December 2001 18:48, Emil Rasmussen wrote:
> > I am not sure I quite understand your problem, but doen't the BETWEEN ...
> > AND operator
> > solve your problem? All you have to do is to format the limits in a way
> > that MySQL understands
> > to be a timestamp, and it should then
On Wednesday 05 Dec 2001 09:30, Emil Rasmussen wrote:
> > CREADE INDEX myDateField_Index
> > ON tblTabel
> > (
> > DATE_FORMAT(myDateField,'%Y%m%d')
> > );
> >
> > This may or may not work, but it's worth a shot, if you prefer your query
>
> in
>
> > the format it was in before.
>
> It did not wor
> I am not sure I quite understand your problem, but doen't the BETWEEN ...
> AND operator
> solve your problem? All you have to do is to format the limits in a way
> that MySQL understands
> to be a timestamp, and it should then do a proper date comparison.
Nope, the BETWEEN AND operator does no
> > CREADE INDEX myDateField_Index
> > ON tblTabel
> > (
> > DATE_FORMAT(myDateField,'%Y%m%d')
> > );
> >
> > This may or may not work, but it's worth a shot, if you prefer your
query
> in
> > the format it was in before.
>
> It did not work with MySQL 3.23.32, it whould have been very nice,
be
> CREADE INDEX myDateField_Index
> ON tblTabel
> (
> DATE_FORMAT(myDateField,'%Y%m%d')
> );
>
> This may or may not work, but it's worth a shot, if you prefer your query
in
> the format it was in before.
It did not work with MySQL 3.23.32, it whould have been very nice, because i
still have a pro
> > Does anyone have some optimization tips for this query:
> >
> > SELECT count(id) FROM tblTabel WHERE fieldName != '' AND
> > DATE_FORMAT(myDateField,'%Y%m%d') = 20011120;
> >
> > It does not use the index i have created, so it scans the entire table,
> > which is a bit to slow.
> >
> > The pro
On Tuesday 04 Dec 2001 14:58, Emil Rasmussen wrote:
> > SELECT count(id)
> > FROM tblTabel
> > WHERE fieldName != '' AND
> > (
> > myDateField >= 2001112000 AND
> > myDateField < 2001113000
> > );
>
> Thank you, that was defiantly part of the solution!
>
> I had created an index on both fi
> SELECT count(id)
> FROM tblTabel
> WHERE fieldName != '' AND
> (
> myDateField >= 2001112000 AND
> myDateField < 2001113000
> );
Thank you, that was defiantly part of the solution!
I had created an index on both fieldName og myDateField, but MySQL did not
use that. But creating an ind
On Tuesday 04 Dec 2001 13:49, Emil Rasmussen wrote:
> Hey
>
> Does anyone have some optimization tips for this query:
>
> SELECT count(id) FROM tblTabel WHERE fieldName != '' AND
> DATE_FORMAT(myDateField,'%Y%m%d') = 20011120;
>
> It does not use the index i have created, so it scans the entire ta
15 matches
Mail list logo
