[horde] Horde Implementation Going South

[Andrew Morgan]

On Thu, 6 Sep 2007, Jan Schneider wrote:

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

On my system, it is using the primary key for both queries.  I'm not sure 
why it doesn't seem to be using the primary key for Dave.  Perhaps this 
example was before he ran ANALYZE TABLE?

 	Andy