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]
