refactor: noteテーブルのインデックス整理と配列カラムへのクエリでインデックスを使うように (#12993)

* Optimize note model index

* enhance(backend): ANY()をやめる (MisskeyIO#239)

* add small e2e test drive endpoint

---------

Co-authored-by: まっちゃとーにゅ <17376330+u1-liquid@users.noreply.github.com>

2 files changed, 5 insertions, 5 deletions

diff --git a/packages/backend/src/server/api/endpoints/notes/mentions.ts b/packages/backend/src/server/api/endpoints/notes/mentions.ts
index 2317f8f7b2..323c6c946b 100644
--- a/packages/backend/src/server/api/endpoints/notes/mentions.ts
+++ b/packages/backend/src/server/api/endpoints/notes/mentions.ts
@@ -61,9 +61,9 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId)
 				.andWhere(new Brackets(qb => {
-					qb
-						.where(`'{"${me.id}"}' <@ note.mentions`)
-						.orWhere(`'{"${me.id}"}' <@ note.visibleUserIds`);
+					qb // このmeIdAsListパラメータはqueryServiceのgenerateVisibilityQueryでセットされる
+						.where(':meIdAsList <@ note.mentions')
+						.orWhere(':meIdAsList <@ note.visibleUserIds');
 				}))
 				// Avoid scanning primary key index
 				.orderBy('CONCAT(note.id)', 'DESC')
diff --git a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
index b00f5207d8..0d7aca6621 100644
--- a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
+++ b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
@@ -87,14 +87,14 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			try {
 				if (ps.tag) {
 					if (!safeForSql(normalizeForSearch(ps.tag))) throw new Error('Injection');
-					query.andWhere(`'{"${normalizeForSearch(ps.tag)}"}' <@ note.tags`);
+					query.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(ps.tag)] });
 				} else {
 					query.andWhere(new Brackets(qb => {
 						for (const tags of ps.query!) {
 							qb.orWhere(new Brackets(qb => {
 								for (const tag of tags) {
 									if (!safeForSql(normalizeForSearch(tag))) throw new Error('Injection');
-									qb.andWhere(`'{"${normalizeForSearch(tag)}"}' <@ note.tags`);
+									qb.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(tag)] });
 								}
 							}));
 						}