PERF: Split index for `PostSearchData#search_data` into PM vs non-PM. (PR #10436)

GitHub

private_post1 and private_post2 has the same ranking so we want to be more intentional here and order by posts.id DESC

The title of this pull request changed from “WIP” to "PERF: Split index for PostSearchData#search_data into PM vs non-PM.

When interpolating strings for SQL it’s always a good idea to add .to_i to avoid injection. I know in this case it would be unusual since it’s coming from a user instance but who knows who will copy and paste or modify this SQL in the future.

If you add a default value of false to the new column you could create the not null index above, avoid the second query below, and the changing of column to not null.

Trivial but you needn’t add semi colons after your SQL statements.

Too bad AR doesn’t support the same interpolation method here as in where("topic_id = ?", topic.id)

This is intentional because I want the migration the fail if any post_seach_data rows do not have the private_message column set to false even after the queries to migrate the data has been written. There is a risk that posts may incorrectly show up in search if we default the column to false.

Merged in https://github.com/discourse/discourse/commit/92b7fe4c62094275c5dd09b1f3dac5c5cbcad1c0

Thank you for reviewing @eviltrout :+1: