Tickets :: Comment on [#4079] horde_prefs table access very slow under high usage

* Your Email Address
* Spam protection	Enter the letters below: .__.. .__ .__ .__ \| \|\| [ __[__)[__) \|__\|\|___[_./\| [__)
Comment	> MySQL does not use either of these indexes and always uses the > primary key index. Chuck confirms this. I also tested Oracle: > > > > SQL> set autotrace traceonly explain > > > > With only the primary key index, the explain output is: > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=35) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HORDE_PREFS' (Cost=4 > Card=1 Bytes=35) > > 2 1 INDEX (RANGE SCAN) OF 'PK_HORDE_PREFS' (UNIQUE) (Cost=3 Card=1) > > > > With the addition of the two one-column indexes, the explain output is: > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=35) > > 1 0 SORT (ORDER BY) (Cost=4 Card=1 Bytes=35) > > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HORDE_PREFS' (Cost=2 > Card=1 Bytes=35) > > 3 2 INDEX (RANGE SCAN) OF 'PREF_UID_IDX' (NON-UNIQUE) > (Cost=1 Card=6) > > > > With the addition of one two-column index, the explain output is: > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=35) > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HORDE_PREFS' (Cost=2 > Card=1 Bytes=35) > > 2 1 INDEX (RANGE SCAN) OF 'PREF_UID_SCOPE_IDX' (NON-UNIQUE) > (Cost=1 Card=1) > >
Attachment
Watch this ticket

Saved Queries