Richard,
Thanks for the reply. I did run ANALYZE and that didn't help. However,
the unary + operator in front of the two utime terms did cause the optimal
index to be used... Can you explain why this works?
I also realized that if I change the "where" clause to something like
where u_id >= 0 and utime >= ..., it will cuase the optimal index to be
used (however, if we every started using negative u_id values, it would
break).
What is the best way to send you sample data (and what is the maximum
file size that you'd like)?
Steve
[EMAIL PROTECTED] wrote:
Steve Green <[EMAIL PROTECTED]> wrote:
[...]
select u_id, sum( data1 ), sum( data2 )
from data where utime >= 1146441600 and utime < 1149120000
group by u_id
order by sum( data1 ) desc
limit 10
[...]
So, is there a way that I can get sqlite to use the optimal index
without having to remove my unique index?
Have you run ANALYZE. That might fix it.
If not, try adding a unary + in front of the two utime
terms in the WHERE clause:
WHERE +utime >= 1145441600 AND +utime < 1149120000
If you can send me some sample data and queries, that would
be great. It will help me to improve the optimizer so that
these kinds of things come up less often in the future.
--
D. Richard Hipp <[EMAIL PROTECTED]>
--
Steve Green
SAVVIS
Transforming Information Technology SM
This message contains information which may be confidential and/or
privileged. Unless you are the intended recipient (or authorized
to receive for the intended recipient), you may not read, use,
copy or disclose to anyone the message or any information contained
in the message. If you have received the message in error, please
advise the sender by reply e-mail at [EMAIL PROTECTED] and
delete the message and any attachment(s) thereto without retaining
any copies.
