On Wed, 9 May 2007 12:23:14 +0200 Peter van Dijk <[EMAIL PROTECTED]> wrote:
> > On 9-mei-2007, at 11:28, bash wrote: > > > SELECT type, stamp_id, old_player_id, new_player_id > > FROM town_log > > WHERE old_player_id = $ID OR new_player_id = $ID > > ORDER BY stamp_id DESC; > > > > This query works really slowly and i don't know why :/ > > For example, the same result by another QUERY work much faster! > > > > SELECT type, stamp_id, old_player_id, new_player_id > > FROM town_log > > WHERE old_player_id = $ID > > UNION > > SELECT type, stamp_id, old_player_id, new_player_id > > FROM town_log > > WHERE new_player_id = %d > > ORDER BY stamp_id DESC; > > Hello Humanoid, > > UNION performing much better than an equivalent query with OR is a > known 'limitation' in many database systems, including MySQL and > SQLite. Page 52 of http://www.sqlite.org/php2004/slides-all.html > explicitly recommends to use UNION here. > > Cheers, Peter. Thanks a lot! I don't know that information :) Moreover this papers is really interesting. But i don't understand about indexes this: http://www.sqlite.org/php2004/slides-all.html Page 54 of 63 For example: CREATE TABLE ex1( id INTEGER PRIMARY KEY, x , y ); CREATE INDEX idx1 ON ex1(x); CREATE INDEX idx2 ON ex1(y); SELECT * FROM ex1 WHERE x>'abc' AND y>'abc'; In this form only one indexes will be used, why not both? For example by idx1 we can get set1 of ROWIDs which is satisfy "x > 'abc'" term, by idx2 we can get set2 of ROWIDs which is satisfy "y>'abc'" term and then just union set1 with set2. So next we should just extracts rows with ROWIDS in this union.... -- Biomechanical Artificial Sabotage Humanoid ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
