[Tickets #12476] Re: Database running mad after update to 5.1.1

[noreply at bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/12476
------------------------------------------------------------------------------
  Ticket             | 12476
  Updated By         | marth at tsvschlieben.de
  Summary            | Database running mad after update to 5.1.1
  Queue              | Horde Framework Packages
  Version            | Git master
  Type               | Bug
  State              | Resolved
  Priority           | 2. Medium
  Milestone          |
  Patch              |
  Owners             | Michael Rubinsky
------------------------------------------------------------------------------


marth at tsvschlieben.de (2013-07-22 08:47) wrote:

Thanks for the feedback - here is what I found out. I suggest to  
reopen the ticket. (see below)

> Since my history data set, even on production is not anywhere near  
> as large as your data set, can you see if adding the following index  
> improves this for you? EXPLAIN now shows that it is using the index  
> so it should improve...
>
> ALTER TABLE horde_histories ADD INDEX (history_modseq, object_uid);

It's now using the index - but the query itself becomes even slower:

Before:

1.13 sec

After:

2.30 sec

(Indeed EXPLAIN shows that the index history_modseq is used)

> If not, the next attempt would be to use something like:
>
>  SELECT history_modseq FROM horde_histories WHERE object_uid LIKE  
> 'turba:%' ORDER BY history_modseq DESC LIMIT 1;

0.00 sec (!!!)

So this solves the speed issue.

> but I'd be wary of doing this since I doubt it would perform the  
> same from one RDBMS to another.

It think it's worth a test because the performance raise in MySQL is  
DRAMATICALLY. The key length (according MySQL explain) that is used is  
now 4 instead of 767. Maybe this could be the reason for the gain.