db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From degenaro <lou.degen...@gmail.com>
Subject Re: slow query? (from a practical newbie)
Date Tue, 07 Jan 2014 14:08:33 GMT
ij> select * from ducc.Job order by stateIndex asc, id desc where id <
117000;
ERROR 42X01: Syntax error: Encountered "where" at line 1, column 57.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed
directly.
Consult your DBMS server reference documentation for details of the SQL
syntax supported by your server.

So I tried:

ij> select * from ducc.Job where id < 117000 order by stateIndex asc, id
desc;

And I see this query plan:

ue Jan 07 09:06:12 EST 2014 Thread[main,5,main] (XID = 10483), (SESSIONID =
1), select * from ducc.Job where id < 117000 order by stateIndex asc, id
desc ******* Sort ResultSet:
Number of opens = 1
Rows input = 5468
Rows returned = 5468
Eliminate duplicates = false
In sorted order = false
Sort information:
        Number of rows input=5468
        Number of rows output=5468
        Sort type=internal
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        optimizer estimated row count: 5453.00
        optimizer estimated cost: 426177.87
Source result set:
        Index Row to Base Row ResultSet for JOB:
        Number of opens = 1
        Rows seen = 5468
        Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
31, 32, 33, 34, 35, 36, 37}
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                optimizer estimated row count: 5453.00
                optimizer estimated cost: 426177.87
                Index Scan ResultSet for JOB using constraint UNIQUECOLUMNS
at read committed isolation level using instantaneous share row locking
chosen by the optimizer
                Number of opens = 1
                Rows seen = 5468
                Rows filtered = 0
                Fetch Size = 16
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        next time in milliseconds/row = 0

                scan information:
                        Bit set of columns fetched=All
                        Number of columns fetched=2
                        Number of deleted rows visited=0
                        Number of pages visited=57
                        Number of rows qualified=5468
                        Number of rows visited=5469
                        Scan type=btree
                        Tree height=2
                        start position:
                                None
                        stop position:
                                >= on first 1 column(s).
                                Ordered null semantics on the following
columns:
                                0
                        qualifiers:
                                None
                        optimizer estimated row count: 5453.00
                        optimizer estimated cost: 426177.87






--
View this message in context: http://apache-database.10148.n7.nabble.com/slow-query-from-a-practical-newbie-tp136285p136320.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Mime
View raw message