CREATE TABLE CODE
(
   CODE_ID INTEGER NOT NULL AUTO_INCREMENT,
   SHORT_NAME VARCHAR_IGNORECASE,
   DESCRIPTION VARCHAR_IGNORECASE,
   CODE_GROUP INTEGER,
   IS_DELETED SMALLINT,
   PARENT_CODE_ID INTEGER,
   SORT_POSITION INTEGER,
   PRIMARY KEY (CODE_ID)
);

INSERT INTO CODE (CODE_ID,SHORT_NAME,DESCRIPTION,CODE_GROUP,IS_DELETED,PARENT_CODE_ID,SORT_POSITION) VALUES (2,'Delivery Term','Delivery Term',1,0,null,null);
INSERT INTO CODE (CODE_ID,SHORT_NAME,DESCRIPTION,CODE_GROUP,IS_DELETED,PARENT_CODE_ID,SORT_POSITION) VALUES (3,'Areas','Areas',1,0,null,null);
INSERT INTO CODE (CODE_ID,SHORT_NAME,DESCRIPTION,CODE_GROUP,IS_DELETED,PARENT_CODE_ID,SORT_POSITION) VALUES (5,'Fuel Oil Type','Fuel Oil Type',1,0,null,null);
INSERT INTO CODE (CODE_ID,SHORT_NAME,DESCRIPTION,CODE_GROUP,IS_DELETED,PARENT_CODE_ID,SORT_POSITION) VALUES (6,'Engine Rotation','Engine Rotation Direction',1,0,null,null);
INSERT INTO CODE (CODE_ID,SHORT_NAME,DESCRIPTION,CODE_GROUP,IS_DELETED,PARENT_CODE_ID,SORT_POSITION) VALUES (7,'Insurance Classification','insurance classification',1,0,null,null);

EXPLAIN ANALYZE SELECT c1.* FROM CODE c1 join CODE c2 on c1.PARENT_CODE_ID = c2.CODE_ID;

DROP INDEX PRIMARY_KEY_1;
CREATE PRIMARY KEY ON CODE(CODE_ID);
ANALYZE SAMPLE_SIZE 0;

EXPLAIN ANALYZE SELECT c1.* FROM CODE c1 join CODE c2 on c1.PARENT_CODE_ID = c2.CODE_ID;
