[horde] Horde Implementation Going South

[Jan Schneider]

Zitat von Andrew Morgan <morgan at orst.edu>:

> On Wed, 5 Sep 2007, Andrew Morgan wrote:
>
>> On Wed, 5 Sep 2007, Dave Cunningham wrote:
>>
>>> I did an analyze on all my tables... they all immediately returned "ok".
>>
>> That means it successfully updated statistics.  If there were not enough
>> changes to require an update, a different message is returned.
>>
>>> I logged a bunch of slow queries.  One of them that returned with  
>>> no keys used:
>>>
>>> mysql> describe SELECT object_id, owner_id, object_type,  
>>> object_members, object_uid, object_jpegphoto, object_name,  
>>> object_email, object_alias, object_category, object_cellphone,  
>>> object_homeaddress, object_homephone, object_homephone2,  
>>> object_personalweb, object_spouse, object_birthdate,  
>>> object_anniversary, object_company, object_profession,  
>>> object_title, object_assistant, object_department, object_manager,  
>>> object_workaddress, object_office, object_workphone,  
>>> object_workphone2, object_businessweb, object_otherphone,  
>>> object_fax, object_faxphone2, object_notes, object_freebusyurl  
>>> FROM turba_objects WHERE object_id IN (0);
>>> +----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
>>> | id | select_type | table         | type | possible_keys | key  |  
>>> key_len | ref  | rows   | Extra       |
>>> +----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
>>> |  1 | SIMPLE      | turba_objects | ALL  | PRIMARY       | NULL |  
>>> NULL    | NULL | 371055 | Using where |
>>> +----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
>>> 1 row in set (0.00 sec)
>>>
>>> Although, the more I look at this, perhaps it makes sense that  
>>> this one has not index reference.
>>
>> Doesn't that reduce to "WHERE object_id = '0'"?  I don't know why it would
>> trigger a full table scan unless object_id wasn't indexed...
>
> Hmmm...  Apparently "IN (0)" is not the same as "IN ('0')".
>
>
> mysql> select count(*) from turba_objects where object_id IN (0);
> +----------+
> | count(*) |
> +----------+
> |    85220 |
> +----------+
> 1 row in set (0.20 sec)
>
> mysql> select count(*) from turba_objects where object_id IN ('0');
> +----------+
> | count(*) |
> +----------+
> |        0 |
> +----------+
> 1 row in set (0.00 sec)
>
>
> I don't understand how "IN (0)" is matching these rows though:
>
> ...
> | zPB9cwkJ9 at VK2NdC^ufsLrWe6DF4$S%  |
> | zpcQd^F$dAjD1rFAw85hpvNoFtzIvQp  |
> | zPF6MNnxZNWTRRFdTHz9DGhGhH3g87y  |
> ...
>
>
> And the result set is not the entire table either.
>

This is strange indeed, and I can reproduce it. Some rows of the table  
are matching "IN (0)" for no apparent reason. Though whether I use "IN  
(0)" or "IN ('0')", the primary key (which is on object_id) is used in  
both cases and no full table scan is happening.

So the questions are: why is your db not using the primary key when  
using "IN (0)"; and even more important: why is turba running that  
query at all, it doesn't make any sense?

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/