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.
