[horde] Horde Implementation Going South

[Andrew Morgan]

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.


I guess my SQL knowledge fails me!

 	Andy