I have a query that is unbearable at scale, for example when s_table_a and s_table_b have 70k and 1.25M rows.
SELECT s.id AS s_id ,s.lid AS s_lid ,sa.val AS s_sid ,d.id AS d_id ,d.lid AS d_lid FROM s_table_b sa JOIN d_table_b da ON ( da.key=sa.key AND da.key='unique_string' AND da.val LIKE sa.val ) JOIN s_table_a s ON s.id=sa.id JOIN d_table_a d ON ( d.id=da.id AND NOT d.lid LIKE s.lid ) I am using LIKE as the columns are indexed NOCASE and I need the comparison case insensitive. I suspect this is where is breaks down but I don't know enough sql to really appreciate the ways I could approach this better. Both {s|d}_table_a have 2 columns, id, lid where id is PK. Both {s|d}_table_b have 4 columns, seqno, id, key, val where seqno is PK, id is a FK ref to {s|d}_table_a.id, and several key/val pairs are inserted to correspond to the associated PK id from {s|d}_table_a. I'd be grateful for any suggestions or hints to improve this. Thanks, jlc _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users