Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Date: Tue, 20 Mar 2007 12:52:12 -0700 (PDT)
From: Dan Karp <dkarp@zimbra.com>
To: derby-user <derby-user@db.apache.org>
Message-ID: <1268876681.82631174420332494.JavaMail.root@dogfood.liquidsys.com>
Subject: Queries satisfiable from indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

I've got a few queries that should be satisfiable from an index without any access to the underlying rows.  Can Derby do this, or does it load the rows regardless?  In this case, would it help things if I'd used DISTINCT(tags) instead of a GROUP BY clause?


QUERY:

  SELECT tags FROM mboxgroup1.mail_item
  WHERE mailbox_id = ?
  GROUP BY mailbox_id, tags;


INDEX:

  CREATE INDEX ${DATABASE_NAME}.i_mail_item_tags_date
    ON mail_item(mailbox_id, tags, date);


PLAN:

Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
	optimizer estimated row count:        14134.80
	optimizer estimated cost:         5049.44

Source result set:
	Sort ResultSet:
	Number of opens = 1
	Rows input = 141512
	Rows returned = 3
	Eliminate duplicates = true
	In sorted order = true
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		optimizer estimated row count:        14134.80
		optimizer estimated cost:         5049.44

	Source result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 141512
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
			optimizer estimated row count:        14134.80
			optimizer estimated cost:         5049.44

		Source result set:
			Index Scan ResultSet for MAIL_ITEM using index I_MAIL_ITEM_TAGS_DATE at read committed isolation level using share row locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 141512
			Rows filtered = 0
			Fetch Size = 1
				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={0, 1}
				Number of columns fetched=2
				Number of deleted rows visited=20458
				Number of pages visited=501
				Number of rows qualified=141512
				Number of rows visited=161970
				Scan type=btree
				Tree height=3
				start position: 
	>= on first 1 column(s).
	Ordered null semantics on the following columns: 

				stop position: 
	> on first 1 column(s).
	Ordered null semantics on the following columns: 

				qualifiers:
None
				optimizer estimated row count:        14134.80
				optimizer estimated cost:         5049.44