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 problem
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
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 not
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 do a
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 sorted
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 can
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
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 either
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
did
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
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 table,
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 index only
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 fieldName og
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 problem off course
14 matches
Mail list logo
