В сообщении от Wednesday 23 July 2008 15:42:04 Igor Tandetnik написал(а):
> "Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>
> > select * from direction_telephony
> > where prefix in
> > ('78312604812','7831260481','783126048','78312604','7831260','783126','78
> >312','7831','783','78','7') order by length(prefix) desc
> > limit 1;
>
> select * from direction_telephony
> where prefix != '' and '78312604812' LIKE prefix || '%'
> order by length(prefix) desc
> limit 1;
>
> Or
>
> where prefix=substr('78312604812', 1, length(prefix))
>
> Igor Tandetnik
And how about indicies?
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix in
('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony WITH INDEX direction_telephony_prefix
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix in
(substr('78312604812',1,1),substr('78312604812',1,2),substr('78312604812',1,3),
...>
substr('78312604812',1,4),substr('78312604812',1,5),substr('78312604812',1,6),substr('78312604812',1,7),
...>
substr('78312604812',1,8),substr('78312604812',1,9),substr('78312604812',1,10),substr('78312604812',1,11),
...>
substr('78312604812',1,12),substr('78312604812',1,13),substr('78312604812',1,14),substr('78312604812',1,15))
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony WITH INDEX direction_telephony_prefix
sqlite>
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix != '' and '78312604812' LIKE prefix || '%'
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony
sqlite>
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix=substr('78312604812', 1, length(prefix))
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
