My bad... I was sure I had created a status index, however, after inspection
it appears I had not.  I created it and presto!  Performance is as good if
not better than when I had +- 20,000 records last week.

Thanks to anyone who concidered commenting,

Larry

-----Original Message-----
From: Larry Brown [mailto:[EMAIL PROTECTED]
Sent: Monday, January 26, 2004 10:27 AM
To: Paul DuBois; MySQL List
Subject: RE: order of elements in where clause


ok, I have a table with 100,000+ records. 98% of these records are jobs that
have been completed.  I have created an index for status.  Status=4 is one
of 4 different states a job can be in prior to completion so we aren't
talking about a lot of records that meet that status level.

query = SELECT field1,field2,field3,field4 FROM table WHERE status=4 and del
< 1 and level = 1

now I don't see a need for creating an index for level since most records
are level 1 nor del since most records are del=0.  However, in order to have
mysql use the status index, do I have to create indexes for those as well?
I did as you suggested with explain and got back...

table   type    possible_keys   key     key_len ref     rows            extra
main    all     null            null    null            null    174620  whereused; 
using filesort

Being new to this I don't know for sure, but isn't this saying that the
index is not being used?

Larry


-----Original Message-----
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Saturday, January 24, 2004 8:14 PM
To: Larry Brown; MySQL List
Subject: Re: order of elements in where clause


At 8:34 -0500 1/24/04, Larry Brown wrote:
>in a select statement, is the order of elements in the where clause the
>determining factor on what records are chosen first, second etc?

No.  The optimizer examines the query to determine the most efficient
way to execute the query. You can get information about how the optimizer
views a SELECT statement by issuing it with the word EXPLAIN prepended:

EXPLAIN select fname from maintable where status=1 and dob > '2001-03-10';

>
>for instance...
>
>select fname from maintable where status=1 and dob > '2001-03-10';
>
>does that mean that the records will first be checked for status creating a
>pool of records with status=1 to then check for dob out of that pool or if
>one of the fields is an index will it automatically use the index first or
>does it use some other ordering methodology?
>
>TIA
>
>Larry
>


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to