A mailing list for Phing user discussions

Murray Collingwood | 23 Jul 2009 03:49

Does anybody understand MySQL?

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 examined?

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"/>
</table>

Cheers
mc

--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au

headers

Justin Carlson | 23 Jul 2009 17:07

Re: Does anybody understand MySQL?

Murray Collingwood 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 examined?
What version of Mysql are you using, what collation?

Latin1 should be case insensitive correct? You should be able to do away 
with the UPPER() for your order by. So unless you're using an old 
version, just remove that.

I'm guessing ORDER BY CONVERT (brand.name USING latin1);  would have the 
same issue, as you're converting while sorting.

If you're forced to use an old version, convert the name to upper/lower 
on insert and apply formatting on display.

------------------------------------------------------
http://propel.tigris.org/ds/viewMessage.do?dsForumId=1097&dsMessageId=2374850

To unsubscribe from this discussion, e-mail: [users-unsubscribe <at> propel.tigris.org].

headers

David Goodwin | 23 Jul 2009 07:32

Re: Does anybody understand MySQL?

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 examined?

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"/>
</table>

Cheers
mc

--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au

headers

Andy Young | 23 Jul 2009 16:58

Re: Does anybody understand MySQL?

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 capitalisation..

-- 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 examined?

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"/>
</table>

Cheers
mc

--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au

--
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

headers

Murray Collingwood | 23 Jul 2009 23:06

Re: Does anybody understand MySQL?

Hi guys

Now this has stimulated another question. My Propel code that generates this code is simply:

        $c = new Criteria();
        $c->addAscendingOrderByColumn(self::NAME);
        return self::doSelect($c);

So how do I tell Propel to turn off UPPER() in the ORDER BY ?

Collation is utf8_general_ci

Cheers
mc

2009/7/24 Andy Young <andy <at> groupspaces.com>

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 capitalisation..

-- 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 examined?

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"/>
</table>

Cheers
mc

--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au

--
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

--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au

headers

Felix Gilcher | 23 Jul 2009 17:01

Re: Does anybody understand MySQL?

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  
> capitalisation..

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.

Cheers

felix

>
> -- 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  
>> examined?
>>
>> 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"/>
>>  </table>
>>
>>
>> Cheers
>> mc
>>
>> -- 
>> Murray Collingwood
>> Focus Computing
>> p +61 415 24 26 24
>> http://www.focus-computing.com.au
>
>
>
> -- 
> 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

------------------------------------------------------
http://propel.tigris.org/ds/viewMessage.do?dsForumId=1097&dsMessageId=2374848

To unsubscribe from this discussion, e-mail: [users-unsubscribe <at> propel.tigris.org].