I’ll put it here for my own reference:
p = Post.scoped
pt = PostsTag.arel_table
pt_arels = 
tags.each do |t|
t_id = Tag.where(:name => t).first[:id]
pt_arels << pt.where(pt[:tag_id].eq(t_id)).project(pt[:post_id])
pt_arels.each do |q|
p = p.where(:id => q)
And in fact, relatively fast.
There’s a problem with nested query I previously mentioned: query time is unstable. The worst case is at least as slow as multiple self-join method – when the first few search key result is too big.
While digging more into the code called “Moebooru” which was forked from “Danbooru”, I noticed this:
connection.execute("update table_data set row_count = row_count + 1 where name = 'users'")
connection.execute("update table_data set row_count = row_count - 1 where name = 'users'")
Counting takes ages, right. Except it is not. I’ve done this, yes, but on a table with 10+ millions of data (this one has ~400k in mainline danbooru), with multiple data inserted (this one got, uh, one every other week?) and queried every second (see below), and with the required count method not a simple
select count(1) on some_table (which is what the example above used for).
The best part? It’s only used once, when user registers:
if User.fast_count == 0
self.level = CONFIG["user_levels"]["Admin"]
self.level = CONFIG["user_levels"]["Unactivated"]
self.level = CONFIG["starting_level"]
self.last_logged_in_at = Time.now
This afternoon I experimented with tag searching in yande.re database. Current implementation doesn’t scale beyond 6 tags due to parsing requirement. You can argue about binding variable all day but in the end it can be abused easily if the limit is lifted.