Re: How can I make this query more optimised?

[Brent Baisley]

Try never to use formulas or functions in your WHERE clause, indexing  
can't be used if you have to perform a function on field data first. It  
looks like you are trying to filter on the id, so you shouldn't be  
using concat and substring in your filter. Change you WHERE clause to  
look like this:
WHERE id='33'

You need to give MySQL, and any database, an opportunity to use  
existing indexes. This means no calculations in your search. You can  
usually use a simple comparison in your WHERE clause and filter it  
further with another WHERE directive or a HAVING clause.

Since your id field is an int and your email field is a char, you  
probably shouldn't create an index combining the two. Create two  
separate indexes. MySQL will optimize your query if you are going to  
search on both id and email.

On Tuesday, July 29, 2003, at 04:13 AM, Karam Chand wrote:

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]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]