Quote:
Originally Posted by cbk1994
more efficient
|
Not necessarily. If that field is not indexed, then it will have to do a sort on the returned rows by moving the result to a temporary table and sorting them there with an internal algorithm that may not be sufficient for your data size, which can be very, very, very expensive when you have a lot of rows. If the field is indexed, then it will be faster because the b-tree index keeps it in order during the insertion/deletion, and it is only matter of reading things sequentially at that point.
Note that the optimizer can only make use of an index on the ORDER BY if it is a composite index covering all the WHERE conditions and ORDER BY conditions.
e.x.,
PHP Code:
SELECT * FROM tbl WHERE a = 'value' ORDER BY b, c;
requires the following index: (a,b,c).
e.x.,
PHP Code:
SELECT * FROM tbl WHERE a = 'value' AND b = 'value' ORDER BY c;
requires the following index: (a,b,c).
The overhead of talking to SQLite and the overhead within the engine also certainly need to be taken into account when doing something small, where it often makes things faster to just manipulate the data in GS2.