schema.md

sqlite3

sqlite> .schema nodes
CREATE TABLE "nodes" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
"type" varchar(255), 
"path" varchar(768) NOT NULL, 
"description" varchar(255), 
"visible" boolean DEFAULT 't' NOT NULL, 
"complex" boolean, 
"created_at" datetime, 
"updated_at" datetime, 
"ancestry" varchar(255), 
"ancestry_depth" integer DEFAULT 0);
CREATE INDEX "index_nodes_on_path" ON "nodes" ("path");
CREATE INDEX "index_nodes_on_type_and_path" ON "nodes" ("type", "path");
sqlite> 

mysql

nodes | CREATE TABLE `nodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `path` varchar(768) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `complex` tinyint(1) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `ancestry` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ancestry_depth` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_nodes_on_path` (`path`(255)),
  KEY `index_nodes_on_type_and_path` (`type`,`path`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

FYI, SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", so SQLite does not enforce the length of a VARCHAR!! This means you can store a 500-million character string to VARCHAR(255).

Does SQLite use the indexes when querying for TEXT?

It looks fine.

sqlite> explain query plan select path from nodes where path = 'a';
0|0|0|SEARCH TABLE nodes USING COVERING INDEX index_nodes_on_path (path=?) (~10 rows)
sqlite> explain query plan select path from nodes where type = 'a' and path = 'a';
0|0|0|SEARCH TABLE nodes USING COVERING INDEX index_nodes_on_type_and_path (type=? AND path=?) (~9 rows)