SQLite에서 많은 작은 질의는 효율적이다

클라이언트/서버 데이터베이스 엔진에서는 웹페이지당 200개의 SQL 문이 과도할 수 있지만, SQLite에서는 그렇지 않아요. 이 문서는 "N+1 질의" 패턴이 SQLite에서는 문제가 되지 않는 이유를 설명해요.

출처: Many Small Queries Are Efficient In SQLite 문서

본문

1. 요약 (Executive Summary)

웹페이지당 200개의 SQL 문은 MySQL, PostgreSQL, SQL Server 같은 클라이언트/서버 데이터베이스 엔진에는 과도해요.
하지만 SQLite에서는 웹페이지당 200개 이상의 SQL 문이 문제가 되지 않아요.
SQLite는 클라이언트/서버 데이터베이스처럼 크고 복잡한 질의도 효율적으로 처리할 수 있어요. 하지만 많은 더 작은 질의도 효율적으로 처리할 수 있어요. 애플리케이션 개발자는 당면한 작업에 가장 잘 맞는 기법을 사용하면 돼요.

2. 인식된 문제

Appropriate Uses For SQLite 페이지는 SQLite 웹사이트의 동적 페이지가 보통 각각 약 200개의 SQL 문을 실행한다고 말해요. 이는 독자들의 비판을 불러일으켰어요. 예를 들면:

"200개의 SQL 문은 단일 페이지에 비해 터무니없이 많은 수다"
"대부분의 사이트에서 200개의 질의는 훨씬, 훨씬, 훨씬 너무 많다."
"[이것은] 나쁜 설계다"

이런 비판은 MySQL, PostgreSQL, SQL Server 같은 전통적인 클라이언트/서버 데이터베이스 엔진에는 타당할 거예요. 클라이언트/서버 데이터베이스에서는 각 SQL 문이 애플리케이션에서 데이터베이스 서버로, 그리고 다시 애플리케이션으로의 메시지 왕복(round-trip)을 요구해요. 200개가 넘는 왕복 메시지를 순차적으로 수행하는 것은 심각한 성능 저하가 될 수 있어요. 이는 때때로 "N+1 질의 문제" 또는 "N+1 선택 문제"라고 불리며 안티 패턴이에요.

3. SQLite에서는 N+1 질의가 문제가 되지 않는다

하지만 SQLite는 클라이언트/서버가 아니에요. SQLite 데이터베이스는 애플리케이션과 같은 프로세스 주소 공간에서 실행돼요. 질의는 메시지 왕복을 포함하지 않고 단지 함수 호출만 할 뿐이에요. 단일 SQL 질의의 지연은 SQLite에서 훨씬 작아요. 따라서 SQLite에서 많은 수의 질의를 사용하는 것은 문제가 되지 않아요.

4. 웹페이지당 200개 이상의 SQL 문이 필요한 이유

SQLite 웹사이트의 동적 웹페이지는 대부분 Fossil 버전 관리 시스템으로 생성돼요. 전형적인 동적 페이지는 https://sqlite.org/src/timeline 같은 타임라인이에요. 타임라인이 사용하는 모든 SQL의 로그는 아래와 같아요.

로그의 첫 번째 질의 그룹은 Fossil 데이터베이스의 "config"와 "global_config" 테이블에서 표시 옵션을 추출해요. 그 다음에는 타임라인에 표시할 모든 요소의 목록을 추출하는 단일 복잡한 질의가 있어요. 이 "timeline" 질의는 SQLite가 여러 테이블, 서브쿼리, 복잡한 WHERE 절 제약을 포함하는 복잡한 관계형 데이터베이스 질의를 쉽게 처리할 수 있고, 최소한의 디스크 I/O로 질의를 해결하기 위해 인덱스를 효과적으로 사용할 수 있음을 보여줘요.

단일 큰 "timeline" 질의 다음에는 각 타임라인 요소에 대한 추가 질의들이 있어요. Fossil은 가능한 한 적은 수의 질의로 모든 정보를 가져오려고 하기보다 "N+1 질의" 패턴을 사용해요. 하지만 불필요한 IPC 오버헤드가 없으므로 그것은 괜찮아요. 각 타임라인 페이지 아래쪽에서 Fossil은 페이지 생성에 걸린 대략적인 시간을 보여줘요. 50개 항목 타임라인의 경우 지연은 보통 25밀리초 미만이에요. 프로파일링에 따르면 그 밀리초 중 데이터베이스 엔진 안에서 보낸 시간은 거의 없어요.

Fossil에서 N+1 질의 패턴을 사용하는 것은 애플리케이션에 해를 끼치지 않아요. 오히려 N+1 질의 패턴에는 이점이 있어요. 첫째, 타임라인 질의를 만드는 코드 부분은 각 타임라인 항목을 표시용으로 준비하는 부분과 완전히 분리될 수 있어요. 이는 코드를 단순하고 유지보수하기 쉽게 유지하는 데 도움이 되는 책임의 분리를 제공해요. 둘째, 표시에 필요한 정보와 그 정보를 추출하는 데 필요한 질의는 표시할 객체의 유형에 따라 달라져요. 체크인(check-in)은 한 세트의 질의가 필요하고, 티켓은 또 다른 세트의 질의가 필요하며, 위키 페이지는 다른 질의가 필요해요. 이런 식으로 계속돼요. 다양한 엔티티를 다루는 코드 부분에서 질의를 요청 시 구현함으로써 책임이 더 분리되고 전체 코드베이스가 단순해져요.

그래서 SQLite는 하나 또는 두 개의 크고 복잡한 질의를 할 수도 있고, 많은 더 작고 단순한 질의를 할 수도 있어요. 둘 다 효율적이에요. 애플리케이션은 당면한 상황에 가장 잘 맞는 것에 따라 두 기법 중 하나 또는 둘 다를 사용할 수 있어요.

다음은 특정 타임라인 하나를 생성하는 데 사용된 모든 SQL의 로그예요(2016-09-16에 캡처됨):

-- sqlite3_open: /home/drh/sqlite/sqlite/.fslckout
PRAGMA foreign_keys=OFF;
SELECT sql FROM localdb.sqlite_schema WHERE name=='vfile';
-- sqlite3_open: /home/drh/.fossil
PRAGMA foreign_keys=OFF;
SELECT value FROM vvar WHERE name='repository';
ATTACH DATABASE '/home/drh/www/repos/sqlite.fossil' AS 'repository' KEY '';
SELECT value FROM config WHERE name='allow-symlinks';
SELECT value FROM global_config WHERE name='allow-symlinks';
SELECT value FROM config WHERE name='aux-schema';
SELECT 1 FROM config WHERE name='baseurl:http://';
SELECT value FROM config WHERE name='ip-prefix-terms';
SELECT value FROM global_config WHERE name='ip-prefix-terms';
SELECT value FROM config WHERE name='localauth';
SELECT value FROM vvar WHERE name='default-user';
SELECT uid FROM user WHERE cap LIKE '%s%';
SELECT login FROM user WHERE uid=1;
SELECT cap FROM user WHERE login = 'nobody';
SELECT cap FROM user WHERE login = 'anonymous';
SELECT value FROM config WHERE name='public-pages';
SELECT value FROM global_config WHERE name='public-pages';
SELECT value FROM config WHERE name='header';
SELECT value FROM config WHERE name='project-name';
SELECT value FROM config WHERE name='th1-setup';
SELECT value FROM global_config WHERE name='th1-setup';
SELECT value FROM config WHERE name='redirect-to-https';
SELECT value FROM global_config WHERE name='redirect-to-https';
SELECT value FROM config WHERE name='index-page';
SELECT mtime FROM config WHERE name='css';
SELECT mtime FROM config WHERE name='logo-image';
SELECT mtime FROM config WHERE name='background-image';
CREATE TEMP TABLE IF NOT EXISTS timeline(
  rid INTEGER PRIMARY KEY,
  uuid TEXT,
  timestamp TEXT,
  comment TEXT,
  user TEXT,
  isleaf BOOLEAN,
  bgcolor TEXT,
  etype TEXT,
  taglist TEXT,
  tagid INTEGER,
  short TEXT,
  sortby REAL
);
INSERT OR IGNORE INTO timeline SELECT
  blob.rid AS blobRid,
  uuid AS uuid,
  datetime(event.mtime,toLocal()) AS timestamp,
  coalesce(ecomment, comment) AS comment,
  coalesce(euser, user) AS user,
  blob.rid IN leaf AS leaf,
  bgcolor AS bgColor,
  event.type AS eventType,
  (SELECT group_concat(substr(tagname,5), ', ') FROM tag, tagxref
    WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid
      AND tagxref.rid=blob.rid AND tagxref.tagtype>0) AS tags,
  tagid AS tagid,
  brief AS brief,
  event.mtime AS mtime
 FROM event CROSS JOIN blob
WHERE blob.rid=event.objid
 AND NOT EXISTS(SELECT 1 FROM tagxref WHERE tagid=5 AND tagtype>0 AND rid=blob.rid)
 ORDER BY event.mtime DESC LIMIT 50;
-- SELECT value FROM config WHERE name='timeline-utc';
SELECT count(*) FROM timeline WHERE etype!='div';
SELECT min(timestamp) FROM timeline;
SELECT julianday('2016-09-15 14:54:51',fromLocal());
SELECT EXISTS (SELECT 1 FROM event CROSS JOIN blob WHERE blob.rid=event.objid AND mtime<=2457647.121412037);
SELECT max(timestamp) FROM timeline;
SELECT julianday('2016-09-24 17:42:43',fromLocal());
SELECT EXISTS (SELECT 1 FROM event CROSS JOIN blob WHERE blob.rid=event.objid AND mtime>=2457656.238009259);
SELECT value FROM config WHERE name='search-ci';
SELECT value FROM vvar WHERE name='checkout';
SELECT value FROM config WHERE name='timeline-max-comment';
SELECT value FROM global_config WHERE name='timeline-max-comment';
SELECT value FROM config WHERE name='timeline-date-format';
SELECT value FROM config WHERE name='timeline-truncate-at-blank';
SELECT value FROM global_config WHERE name='timeline-truncate-at-blank';
SELECT * FROM timeline ORDER BY sortby DESC;
SELECT value FROM config WHERE name='hash-digits';
SELECT value FROM global_config WHERE name='hash-digits';
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68028;
SELECT pid FROM plink WHERE cid=68028 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=68028 AND tagid=9 AND tagtype>0;
SELECT value FROM config WHERE name='timeline-block-markup';
SELECT value FROM config WHERE name='timeline-plaintext';
SELECT value FROM config WHERE name='wiki-use-html';
SELECT value FROM global_config WHERE name='wiki-use-html';
SELECT 1 FROM private WHERE rid=68028;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68026;
SELECT pid FROM plink WHERE cid=68026 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68026;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68024;
SELECT pid FROM plink WHERE cid=68024 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68024;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68018;
SELECT pid FROM plink WHERE cid=68018 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68018;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68012;
SELECT pid FROM plink WHERE cid=68012 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68012;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68011;
SELECT value FROM config WHERE name='details';
SELECT pid FROM plink WHERE cid=68011 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=68011 AND tagid=9 AND tagtype>0;
SELECT 1 FROM private WHERE rid=68011;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68008;
SELECT pid FROM plink WHERE cid=68008 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68008;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68006;
SELECT pid FROM plink WHERE cid=68006 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68006;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68000;
SELECT pid FROM plink WHERE cid=68000 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68000;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67997;
SELECT pid FROM plink WHERE cid=67997 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67997;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67992;
SELECT pid FROM plink WHERE cid=67992 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67992;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67990;
SELECT pid FROM plink WHERE cid=67990 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67990;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67989;
SELECT pid FROM plink WHERE cid=67989 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67989;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67984;
SELECT pid FROM plink WHERE cid=67984 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67984;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67983;
SELECT pid FROM plink WHERE cid=67983 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67983;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67979;
SELECT pid FROM plink WHERE cid=67979 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67979;
SELECT value FROM config WHERE name='ticket-closed-expr';
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67980;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67977;
SELECT pid FROM plink WHERE cid=67977 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67977;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67974;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67971;
SELECT pid FROM plink WHERE cid=67971 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67971;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67972;
SELECT pid FROM plink WHERE cid=67972 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67972;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67969;
SELECT pid FROM plink WHERE cid=67969 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67969;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67966;
SELECT pid FROM plink WHERE cid=67966 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67966;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67962;
SELECT pid FROM plink WHERE cid=67962 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67962;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67960;
SELECT pid FROM plink WHERE cid=67960 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67960;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67957;
SELECT pid FROM plink WHERE cid=67957 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67957;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67955;
SELECT pid FROM plink WHERE cid=67955 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67955;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67953;
SELECT pid FROM plink WHERE cid=67953 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='5990a1bdb4a073' AND tkt_uuid<'5990a1bdb4a074';
SELECT 1 FROM blob WHERE uuid>='5990a1bdb4a073' AND uuid<'5990a1bdb4a074';
SELECT 1 FROM private WHERE rid=67953;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67941;
SELECT pid FROM plink WHERE cid=67941 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67941;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67940;
SELECT pid FROM plink WHERE cid=67940 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67940;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67938;
SELECT pid FROM plink WHERE cid=67938 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67938;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67935;
SELECT pid FROM plink WHERE cid=67935 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67935;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67934;
SELECT pid FROM plink WHERE cid=67934 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67934;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67932;
SELECT pid FROM plink WHERE cid=67932 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67932;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67930;
SELECT pid FROM plink WHERE cid=67930 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67930;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67928;
SELECT pid FROM plink WHERE cid=67928 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=67928 AND tagid=9 AND tagtype>0;
SELECT 1 FROM private WHERE rid=67928;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f511d' AND tkt_uuid<'0eab1ac7591f511e';
SELECT 1 FROM private WHERE rid=67919;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='01874d252ac44861' AND tkt_uuid<'01874d252ac44862';
SELECT 1 FROM blob WHERE uuid>='01874d252ac44861' AND uuid<'01874d252ac44862';
SELECT 1 FROM private WHERE rid=67918;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67916;
SELECT pid FROM plink WHERE cid=67916 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac759' AND tkt_uuid<'0eab1ac75:';
SELECT 1 FROM private WHERE rid=67916;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='a49bc0a8244feb08' AND tkt_uuid<'a49bc0a8244feb09';
SELECT 1 FROM blob WHERE uuid>='a49bc0a8244feb08' AND uuid<'a49bc0a8244feb09';
SELECT 1 FROM private WHERE rid=67914;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67913;
SELECT pid FROM plink WHERE cid=67913 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f' AND tkt_uuid<'0eab1ac7591g';
SELECT 1 FROM private WHERE rid=67913;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67911;
SELECT pid FROM plink WHERE cid=67911 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67911;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f511d' AND tkt_uuid<'0eab1ac7591f511e';
SELECT 1 FROM private WHERE rid=67909;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67907;
SELECT pid FROM plink WHERE cid=67907 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67907;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67899;
SELECT pid FROM plink WHERE cid=67899 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67899;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67897;
SELECT pid FROM plink WHERE cid=67897 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67897;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67895;
SELECT pid FROM plink WHERE cid=67895 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67895;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67893;
SELECT pid FROM plink WHERE cid=67893 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67893;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67891;
SELECT pid FROM plink WHERE cid=67891 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67891;
SELECT count(*) FROM plink
 WHERE pid=67928 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT count(*) FROM plink
 WHERE pid=68011 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT count(*) FROM plink
 WHERE pid=68028 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT value FROM config WHERE name='show-version-diffs';
SELECT value FROM config WHERE name='adunit-omit-if-admin';
SELECT value FROM global_config WHERE name='adunit-omit-if-admin';
SELECT value FROM config WHERE name='adunit-omit-if-user';
SELECT value FROM global_config WHERE name='adunit-omit-if-user';
SELECT value FROM config WHERE name='adunit';
SELECT value FROM global_config WHERE name='adunit';
SELECT value FROM config WHERE name='auto-hyperlink-delay';
SELECT value FROM global_config WHERE name='auto-hyperlink-delay';
SELECT value FROM config WHERE name='footer';
PRAGMA database_list;
PRAGMA database_list;
PRAGMA localdb.freelist_count;
PRAGMA localdb.page_count;

더 알아보기 (Learn more)

Appropriate Uses For SQLite
SQLite 질의 최적화: The SQLite Query Optimizer Overview
Prepared Statement C 인터페이스