Greetings
I have a table with the following table structure -
mysql> desc email_table;
+---------+---------------+-------------------+------+-----+---------+-------+
| Field | Type | Collation | Null |
Key | Default | Extra |
+---------+---------------+-------------------+------+-----+---------+-------+
| email | varchar(50) | latin1_swedish_ci | |
PRI | | |
| is_sent | enum('Y','N') | latin1_swedish_ci | YES |
| NULL | |
| id | int(10) | binary | |
PRI | 0 | |
+---------+---------------+-------------------+------+-----+---------+-------+
I have a index on (id,email)....
I do a query like with Explain...
explain select
concat(sum(conv(substring(md5(concat(email,is_sent,id)),1,8),16,10)),
sum(conv(substring(md5(concat(email,is_sent,id)),9,8),16,10)))
as b,
substring(concat(id,email),1,3) as c,
count(*) as cnt from test.email_table
where substring(concat(id,email),1,2) = '33'
group by substring(concat(id,email),1,3)
order by 1;
+----+-------------+-------------+------+---------------+------+---------+------
+-------+----------------------------------------------+
| id | select_type | table | type |
possible_keys | key | key_len | ref
| rows | Extra
|
+----+-------------+-------------+------+---------------+------+---------+------
+-------+----------------------------------------------+
| 1 | SIMPLE | email_table | ALL | NULL
| NULL | NULL | NULL
| 33914 | Using where; Using temporary; Using filesort
|
+----+-------------+-------------+------+---------------+------+---------+------
+-------+----------------------------------------------+
The Explain says that it is not using any query and
scanning 33914 rows ( that is the number of rows in my
table ).
When I execute the query, it takes around 80ms,
returns 10 rows. I am running MySQL 4.1.0-alpha-max-nt
on WinXP.
Is there any way to optimise this query more.....or
the time taken by MySQL is GOOD?
Thanks in advance.
karam
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
