Re: Does anybody understand MySQL?
Felix Gilcher <propel <at> andersground.net>
2009-07-23 15:01:55 GMT
On Jul 23, 2009, at 4:58 PM, Andy Young wrote:
> The problem is using UPPER() in the ORDER BY. Even if you have an
> index on brand.name, applying UPPER (or any function) to the column
> results in calculation of a new value for each row, which is not
> stored in an index - so to sort on that column it first has to
> calculate a new temporary table containing all the values of
> UPPER(brand.name) (using EXPLAIN should show that the query is
> "using temporary") and then pass over them again to sort.
> You can probably get away with just removing UPPER() from the order
> by clause since I'm guessing you don't need to sort by
This is especially true since sorting by UPPER(strinvalue) is a common
trick to get case-insensitive sorting and MySQL by default uses a case
insensitive string ordering. So that's redundant anyways.
> -- A
> On Thu, Jul 23, 2009 at 6:32 AM, David Goodwin
> <david <at> palepurple.co.uk> wrote:
> How many rows are examined I'd the 'order by' is not there?
> From David.....
> On 23 Jul 2009, at 02:49, Murray Collingwood <murray <at> focus-computing.com.au
> > wrote:
>> Hi Propellers
>> This is more an interest thing than a problem, so please don't
>> sweat any time over it.
>> An entry from my slow-query log file:
>> # Time: 090723 9:05:49
>> # User <at> Host: cabinets[cabinets] <at> localhost 
>> # Query_time: 0 Lock_time: 0 Rows_sent: 81 Rows_examined: 162
>> use focus_capext;
>> SELECT brand.ID, brand.NAME, UPPER(brand.NAME) FROM brand ORDER BY
>> UPPER(brand.NAME) ASC;
>> The table has 81 records. How does it happen that 162 rows are
>> The structure is real basic:
>> <table name="brand" description="Product brand where necessary">
>> <column name="id" type="integer" primaryKey="true"
>> autoIncrement="true" required="true"/>
>> <column name="name" type="varchar" size="50"
>> description="Brandname eg Bosch"/>
>> Murray Collingwood
>> Focus Computing
>> p +61 415 24 26 24
> Andy Young
> Co-Founder & CTO, GroupSpaces Ltd :: www.groupspaces.com
> Oxford Centre for Innovation
> Mill Street, Oxford OX2 0JX
> Executive Advisor, Oxford Entrepreneurs :: www.oxfordentrepreneurs.co.uk
To unsubscribe from this discussion, e-mail: [users-unsubscribe <at> propel.tigris.org].