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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
