I just want to add that this SQL query works great at 3.5.4.
And this is explain:
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 EXPLAIN SELECT
town.id, town_log.new_player_id, player.name
FROM
town_log
LEFT JOIN town
LEFT JOIN player
ON
town.id = town_log.town_id AND town_log.new_player_id = player.id
WHERE
town_log.id = 5195; 00
1 Integer 5195 1 0 00
2 Goto 0 46 0 00
3 SetNumColumns 0 5 0 00
4 OpenRead 0 14 0 00
5 SetNumColumns 0 1 0 00
6 OpenRead 1 6 0 00
7 SetNumColumns 0 2 0 00
8 OpenRead 2 2 0 00
9 SetNumColumns 0 2 0 00
10 OpenRead 3 3 0 keyinfo(1,BINARY) 00
11 MustBeInt 1 41 0 00
12 NotExists 0 41 1 00
13 Integer 0 3 0 00
14 Rewind 1 38 0 00
15 Integer 1 3 0 00
16 Integer 0 4 0 00
17 Column 0 4 6 00
18 IsNull 6 33 0 00
19 Affinity 6 1 0 db 00
20 MoveGe 3 33 6 1 00
21 IdxGE 3 33 6 1 01
22 IdxRowid 3 10 0 00
23 MoveGe 2 0 10 00
24 Column 1 0 10 00
25 Column 0 1 11 00
26 Ne 11 32 10 collseq(BINARY) 6b
27 Integer 1 4 0 00
28 Column 1 0 12 00
29 Column 0 4 13 00
30 Column 2 1 14 00
31 ResultRow 12 3 0 00
32 Next 3 21 0 00
33 IfPos 4 37 0 00
34 NullRow 2 0 0 00
35 NullRow 3 0 0 00
36 Goto 0 27 0 00
37 Next 1 15 0 00
38 IfPos 3 41 0 00
39 NullRow 1 0 0 00
40 Goto 0 15 0 00
41 Close 0 0 0 00
42 Close 1 0 0 00
43 Close 2 0 0 00
44 Close 3 0 0 00
45 Halt 0 0 0 00
46 Transaction 0 0 0 00
47 VerifyCookie 0 92 0 00
48 TableLock 0 14 0 town_log 00
49 TableLock 0 6 0 town 00
50 TableLock 0 2 0 player 00
51 Goto 0 3 0 00
PS:
And the last one. I want to say that generally SQLite-3.6.3 is slowly
than 3.5.4 on my OS X 10.5.5.
My result is that 3.6.3 is slowly near 5-7%.
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
On Sat, Sep 20, 2008 at 8:45 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
> Hello everyone,
>
> I gets strange result from this query on SQLite-3.6.2
>
> SELECT
> town.id, town_log.new_player_id, player.name
> FROM
> town_log
> LEFT JOIN town
> LEFT JOIN player
> ON
> town.id = town_log.town_id AND town_log.new_player_id = player.id
> WHERE
> town_log.id = 5195
>
> As you can see this query should return only 1 row, because
> town_log.id is unique key.
> But I gets rows with different town.id (1 col), with constant
> new_player.id (2 col) and JOIN do not work for player.name, couse 3
> col is empty.
>
> result:
> [....]
> 45512 9266
> 44544 9266
> 45229 9266
> 46376 9266
> 45927 9266
> 46645 9266
> [....]
>
>
> Any ideas what's going on?
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
