date_format best choice?

[mrdance]

I want to retrieve some values within this month. Is date_format the most effective way? Or is it any sql that cost less?

SELECT SUM(trades) trades
FROM stats_trades
WHERE DATE_FORMAT(date_trade,'%Y%m') = DATE_FORMAT(now(),'%Y%m')

thanks / Henrik

--- neteject.com - Internet Solutions ---

 

[TonyGroves]

That should work fine, except that it might be slow because it won't use any index. To allow an index to be used, you could use something like:
[tt]
WHERE date_trade LIKE date_format(CURDATE(),'%Y%m%')
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Sorry! That should be:
[tt]
WHERE date_trade LIKE DATE_FORMAT(CURDATE(),'%Y-%m-%')
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[mrdance]

Strangely, your sql took 32 s (10.000.000 rows) and my sql took 28.

regards / Henrik

--- neteject.com - Internet Solutions ---

 

[TonyGroves]

Have you an index on date_trade?

-----
ALTER world DROP injustice, ADD peace;

 

[mrdance]

yes, I have

regards / Henrik

--- neteject.com - Internet Solutions ---

 

[TonyGroves]

Just to be sure that the index is being used, you could do:
[tt]
EXPLAIN SELECT ...
[/tt]
which will tell you if it is or not. That's the best I can come up with, I'm afraid!

-----
ALTER world DROP injustice, ADD peace;

 

[ericbrunson]

You are using a calculated result in your where clause, which is going to invalidate any indexes on that column.

If you are going to do this sort of query a lot then you should consider adding a indexed field with the date_format pre-calculated and searching on it.

There's not really much you can do about it otherwise.

 

[r937]

bingo on the "calculated result" explanation

leave the date field as a date field and don't try to do character string comparisons on it!!

don't do anything that will cause a datatype conversion

do something like the following (written quickly, not tested) --

where date_trade 
  between date_add(current_date
    , interval -dayofmonth(current_date)+1 day)
  and date_add(
        date_add(
          date_add(current_date
    , interval -dayofmonth(current_date)+1 day)
    , interval 1 month)
    , interval -1 day)

all the shenanigans in the BETWEEN simply resolve to the first day of the current month and the last (taking into consideration feb 29, etc.)

thus the date field is compared to date fields and the index will be used

rudy

http://r937.com/

SQL Consulting

 

[TonyGroves]

In my solution, an index on the date field would normally be used.
An [tt]EXPLAIN SELECT ...[/tt] will confirm that, as it did when I tested it.


-----
ALTER world DROP injustice, ADD peace;

 

[KarveR]

all this seems (to the untrained eye) like overkill ,

WHERE DATE_FORMAT(date_trade,'%Y%m') = DATE_FORMAT(now(),'%Y%m')
whatever happened to :

WHERE date_trade like '2004-08-%'

if it needs to be flexible, ny date_foirmat both fdate fields why not just you simple queries ?

WHERE date_trade like date_format(curdate(),'%Y-%m'); as it should be stored?

have I missed something ?





______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

no, in mysql it seems to be not only perfectly okay, but also does not seelm to involve any data conversions to treat a date as a string

but in other databases you will get a syntax error

yes, i realize that this is the mysql forum

i'm just saying it's not a good idea to learn techniques that are non-standard

rudy

http://r937.com/

SQL Consulting