Hi,
Can you do "DESCRIBE QUERY PLAN <your_query>" and post results here?
Also, what do you mean by "unbearable at scale"? Did you measure it? What
is the result?
Thank you.
On Sun, Sep 1, 2013 at 6:03 PM, Joseph L. Casale
<jcas...@activenetwerx.com>wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
