R Fields wrote:
[snip]
> Hi Mark,
>
> You're absolutely right.
>
> mysql> explain select
> DATE_FORMAT(account_creation_date, '%Y-%m-%d') as
> date, count(*) as count from customer where
> customer_deleted_flag = 0 and affiliate_company_id
> = 'FRM' and account_creation_date >=
> '2005-03-02 00:00:00' and account_creation_date
> <= now() group by date;
> +----+-------------+----------+-------+-----------
> --------+-------------------+---------+------+----
> --+----------------------------------------------+
>
> | id | select_type | table | type |
> possible_keys | key | key_len |
> ref | rows | Extra
> |
> +----+-------------+----------+-------+-----------
> --------+-------------------+---------+------+----
> --+----------------------------------------------+
>
> | 1 | SIMPLE | customer | range |
> idx_acdate_cdflag | idx_acdate_cdflag | 12 |
> NULL | 1 | Using where; Using temporary; Using
> filesort |
> +----+-------------+----------+-------+-----------
> --------+-------------------+---------+------+----
> --+----------------------------------------------+
>
> 1 row in set (0.00 sec)
>
>
> and the index:
> | customer | 1 | idx_acdate_cdflag
> | 1 | ACCOUNT_CREATION_DATE | A
> | 16 | NULL | NULL | |
> BTREE | |
> | customer | 1 | idx_acdate_cdflag
> | 2 | CUSTOMER_DELETED_FLAG | A
> | 16 | NULL | NULL | |
> BTREE | |
>
>
> Thanks,
> -Richard
Richard,
If you can't attach the dump of this table to the bug report (which is totally understood), what would _really_ help me is if you didn't mind stepping into a debugger, setting a breakpoint right after you execute your query, and then tell me what the values for rs.fields[0].charsetIndex, .charsetName and .mysqlType are.
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html