[horde] Horde Implementation Going South

[Dave Cunningham]

Unfortunately, this was the same after running both an analyze and an optimize on the tables...

This still comes in with no key.

And, I think my database may be running even slower today, although that may be due to more load.

Dave


-----Original Message-----
From: horde-bounces at lists.horde.org on behalf of Andrew Morgan
Sent: Thu 9/6/2007 12:10 PM
To: Jan Schneider
Cc: horde at lists.horde.org
Subject: Re: [horde] Horde Implementation Going South
 
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
-- 
Horde mailing list - Join the hunt: http://horde.org/bounties/#horde
Frequently Asked Questions: http://horde.org/faq/
To unsubscribe, mail: horde-unsubscribe at lists.horde.org