StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POselect statement extremly slow
text
Body
copied!<p>I'm creating 4 tables and select from them afterwards. Selecting works perfectly for the first 3 select statements, but the 4th one takes about 10 seconds on the iPhone simulator, and 5 seconds on the sqlite3 console. Also I get 0 results on the iPhone simulator, but 1 on the console. But that's a problem I want to solve after I solved the performance issue.</p> <p>I read something about indexes and how they can improve the performance, but I have no clue how to implement them in my code.</p> <pre><code>sql0 = [[NSString alloc]initWithFormat:@" create table v%i as select id_produkt from v%i natural join produkt_eigenschaft where id_eigenschaft = (select id_eigenschaft from eigenschaft where at = '%@')",counter,counter-1,selectedStringItem]; </code></pre> <p>and afterwards:</p> <pre><code>NSString *sqleig = [[NSString alloc]initWithFormat:@" select at from eigenschaft where id_eigenschaft IN (select distinct id_eigenschaft from produkt_eigenschaft where id_produkt IN (select * from v%i)) AND rubrik = '%i'",counter-1, [sender tag] + 1]; </code></pre> <p>Why is this statement executed so slowly? And how can I solve it? Thanks in advance.</p> <p>EDIT: explain query plan and .schema</p> <pre><code>explain query plan create table v3 as select id_produkt from v2 natural join produkt_eigenschaft where id_eigenschaft = (select id_eigenschaft from eigenschaft where at = '101-170'); 0|0|1|SCAN TABLE produkt_eigenschaft (~100000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE eigenschaft USING AUTOMATIC COVERING INDEX (at=?) (~7 rows) 0|1|0|SEARCH TABLE v2 USING AUTOMATIC COVERING INDEX (id_produkt=?) (~7 rows) explain query plan select at from eigenschaft where id_eigenschaft IN (select distinct id_eigenschaft from produkt_eigenschaft where id_produkt IN (select * from v3)) AND rubrik = '5'; 0|0|0|SCAN TABLE eigenschaft (~10000 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|0|SCAN TABLE produkt_eigenschaft (~100000 rows) 1|0|0|EXECUTE LIST SUBQUERY 2 2|0|0|SCAN TABLE v3 (~1000000 rows) 1|0|0|USE TEMP B-TREE FOR DISTINCT CREATE TABLE eigenschaft (id_eigenschaft integer,rubrik integer,en text,at text,ba text,bg text,hr text,cz text,hu text,pl text,ro text,ru text,rs text,sk text,si text); CREATE TABLE farbe (id_farbe integer,hexcode text,farbton integer,farbname text); CREATE TABLE produkt (id_produkt integer,code text,pdf_link text,image_link text,image_small blob,link text,en text,at text,ba text,bg text,hr text,cz text,hu text,pl text,ro text,ru text,rs text,sk text,si text,active integer); CREATE TABLE produkt_eigenschaft (id_produkt integer,id_eigenschaft integer); CREATE TABLE produkt_farbe (id_produkt integer,id_farbe integer); CREATE TABLE produkt_surface (id_surface integer,id_produkt integer,image_link text); CREATE TABLE produkt_text (id_produkt integer,en text,at text,ba text,bg text,hr text,cz text,hu text,pl text,ro text,ru text,rs text,sk text,si text); CREATE TABLE rubrik (id integer,en text,at text,ba text,bg text,hr text,cz text,hu text,pl text,ro text,ru text,rs text,sk text,si text); CREATE TABLE v0(id_produkt INT); CREATE TABLE v1(id_produkt INT); CREATE TABLE v2(id_produkt INT); CREATE TABLE v3(id_produkt INT); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload