Re: [sqlite] [EXTERNAL] If two orders are both the same order?
I take it that AN is the primary key of table ART.
The query shown has 2 possible solutions:
a) SCAN table ART, then SEARCH table XPOST on (GNAME=?,AN=?,TIME>=?)
b) SEARCH table XPOST on (GNAME=?), check if TIME matches, SEARCH table ART on
(AN=?)
Plan a means a full table scan of ART in the hopes of finding a matching XPOST,
which is probably a waste of time for most ART records.
Plan b means a partial table scan of XPOST (records matching GNAME), which is
still probably a waste of time for most retrieved XPOSTrecords.
An Index on (GNAME,TIME) would allow a new plan:
c) SEARCH table XPOST on covering index (GNAME=?,TIME>=?), SEARCH table ART on
(AN=?)
Plan c retrieves only the XPOST records matched by the condition and the single
record from ART that matches the AN (which probably exists by definition).
NOTE: There is usually no need to qualify identifiers. If you feel the urge to
do this anyway, please use double quotes. Single quotes are used for strings.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von sql...@zzo38computer.org
Gesendet: Montag, 06. Mai 2019 18:23
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] If two orders are both the same order?
sqlite-users@mailinglists.sqlite.org wrote:
> Your schema implies that there can be more than one TIME for any GNAME
> and AN combination (otherwise the primary key would not need to
> include alle three fields). This contradicts your statement that AN and TIME
> are "the same order".
> (consider the tuples ("T1",1,1) and ("T2",1,2); the AN field compares
> equal, so ORDER BY AN is free to return the T2 row before the T1 row).
>
> Which query specifically would you have in mind that relies on your assertion?
>
> Also, if your application requires that rows be returned in a specifc
> order, your MUST specify this with en ORDER BY clause and not rely on
> the visitation order. The visitation order may change due to a number
> of factors including the SQLite version, the "shape" of your data, running
> ANALYZE and maybe more.
About the PRIMARY KEY you are correct; that is my mistake.
The specific query is this one:
SELECT `ART`.`MID` FROM `XPOST`, `ART` USING(`AN`) WHERE `XPOST`.`TIME` >= ?1
AND `XPOST`.`GNAME` = ?2;
(The (GNAME,AN) combinations are actually unique, for any value of AN there is
exactly one value of TIME. Probably TIME doesn't really belong in XPOST at all;
I originally put it there due to this confusion I had and then forgot to remove
it; that is also why it is part of the primary key even though it shouldn't be.
The next version of my software would probably fix that.)
The above query implements the NEWNEWS command of NNTP. RFC 3977 says "the
order of the response has no specific significance and may vary from response
to response in the same session"; so, in order that SQLite can choose the most
efficient query plan without requiring a specific order, there is no ORDER BY
clause.
(There is another variant of that query without the second part of the WHERE
clause, used if "NEWNEWS *" is specified. NEWNEWS followed by anything other
than * or a single newsgroup currently results in a 503 error in this
implementation.) ___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] If two orders are both the same order?
sqlite-users@mailinglists.sqlite.org wrote:
> Your schema implies that there can be more than one TIME for any GNAME and AN
> combination (otherwise the primary key would not need to include alle three
> fields). This contradicts your statement that AN and TIME are "the same
> order".
> (consider the tuples ("T1",1,1) and ("T2",1,2); the AN field compares equal,
> so
> ORDER BY AN is free to return the T2 row before the T1 row).
>
> Which query specifically would you have in mind that relies on your assertion?
>
> Also, if your application requires that rows be returned in a specifc order,
> your MUST specify this with en ORDER BY clause and not rely on the visitation
> order. The visitation order may change due to a number of factors including
> the
> SQLite version, the "shape" of your data, running ANALYZE and maybe more.
About the PRIMARY KEY you are correct; that is my mistake.
The specific query is this one:
SELECT `ART`.`MID` FROM `XPOST`, `ART` USING(`AN`) WHERE `XPOST`.`TIME` >= ?1
AND `XPOST`.`GNAME` = ?2;
(The (GNAME,AN) combinations are actually unique, for any value of AN there is
exactly one value of TIME. Probably TIME doesn't really belong in XPOST at all;
I originally put it there due to this confusion I had and then forgot to remove
it; that is also why it is part of the primary key even though it shouldn't be.
The next version of my software would probably fix that.)
The above query implements the NEWNEWS command of NNTP. RFC 3977 says "the
order of the response has no specific significance and may vary from response
to response in the same session"; so, in order that SQLite can choose the most
efficient query plan without requiring a specific order, there is no ORDER BY
clause.
(There is another variant of that query without the second part of the WHERE
clause, used if "NEWNEWS *" is specified. NEWNEWS followed by anything other
than * or a single newsgroup currently results in a 503 error in this
implementation.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] If two orders are both the same order?
Your schema implies that there can be more than one TIME for any GNAME and AN
combination (otherwise the primary key would not need to include alle three
fields). This contradicts your statement that AN and TIME are "the same order".
(consider the tuples ("T1",1,1) and ("T2",1,2); the AN field compares equal, so
ORDER BY AN is free to return the T2 row before the T1 row).
Which query specifically would you have in mind that relies on your assertion?
Also, if your application requires that rows be returned in a specifc order,
your MUST specify this with en ORDER BY clause and not rely on the visitation
order. The visitation order may change due to a number of factors including the
SQLite version, the "shape" of your data, running ANALYZE and maybe more.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von sql...@zzo38computer.org
Gesendet: Sonntag, 05. Mai 2019 21:56
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] If two orders are both the same order?
I have a schema with the following definition:
CREATE TABLE "XPOST"("GNAME" TEXT, "AN" INT, "TIME" INT, PRIMARY KEY
("GNAME", "AN", "TIME")) WITHOUT ROWID;
However, the order by "AN" and the order by "TIME" will be the same order.
(I also have a table "ART" where "AN" is the rowid, and again the order by
"TIME" will be the same order.)
How can you make SQLite to make that assumption in order to optimize the query?
(It should be done presumably without adding another index, since the data is
already in the correct order.)
(This is my "sqlnetnews" NNTP server software, which is public domain open
source. I don't know if maybe you might want to use NNTP for your mailing
lists?) ___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
