From: Date: January 3 2008 4:04pm
Subject: Re: what should an assert_efficient_sql check for?
List-Archive: http://lists.mysql.com/internals/35255
Message-Id: <477CF97B.4010400@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

[Please configure this mailing list to reply to the group!]

Jay Pipes wrote:

> Seems to me that rings and accessories *would* have an index in common, 
> as shown above: the rings.id column is related to the accessories.id 
> column, no?

The query joins Apples and Oranges! ("Rings" could be called "Arenas" - they are 
not jewelry accessories!)

> It has an ALL because there is no WHERE condition supplied on an indexed 
> column, no because of the join.

Point; I will upgrade the sample.

> This is a good basic check for SQL inefficiency, as it checks for full 
> table scans, which are indicators that an index is missing or a WHERE 
> condition is not being passed.  However, there are *many* cases when 
> full table scans are perfectly fine:
> 
> * SELECTing all of a lookup table

For these edge cases, one would not use assert_efficient_sql (or one would make 
the queries efficient anyway). You have identified Z in my A,B,C progression - 
the option before A!

Thanks for the post; I will implement as much as I can now and get back to y'all!

-- 
   Phlip