Thank you very much Jeremy.
I changed to this:
@db[:threads]
.where(:user_id => 9)
.exclude(:id => @db[:posts].where(:user_id => 9).select(:thread_id))
.delete()
And it runs as fast as raw SQL.
Just a feedback, for my first version without distinct(), it was still very
slow. From the principle of least astonishment
<https://en.wikipedia.org/wiki/Principle_of_least_astonishment>, I think it
should work like the raw SQL.
Thank you very much for you help.
On Tuesday, October 31, 2017 at 8:14:21 PM UTC+7, Jeremy Evans wrote:
>
> On Tuesday, October 31, 2017 at 2:10:31 PM UTC+1, Lincoln Hawk wrote:
>>
>> Hi,
>>
>> I have a small database with about 45,000 rows of all tables.
>>
>> I'm using other names for the tables here. They look like this:
>>
>> users
>> └── threads
>> └── posts
>>
>> For user #9, they have about 80 threads, 80 posts. Running this inside
>> MariaDB interactive command line is very fast (<= 1 second):
>>
>> -- for user #9: delete threads without any posts
>> delete from threads where user_id=9 and id not in (select thread_id from
>> posts where user_id=9);
>>
>> But using Sequel, it took ~ 6 minutes:
>>
>> @db[:threads]
>> .where(:id => @db[:posts].distinct.where(:user_id =>
>> 9).select(:thread_id))
>> .invert()
>> .where(:user_id => 9)
>> .delete()
>>
>> Would you please help me with a better Sequel usage for my goal? I
>> checked the docs but couldn't find some hint about #select(). Readme on
>> project page also didn't mention this case.
>>
>
> The only thing that looks like it would make a difference in your Sequel
> code is that you are using distinct when you weren't using it in SQL. You
> could try removing distinct. Also, while it doesn't matter in terms of the
> result, you should prefer using exclude to where followed by invert.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
