[bugfix] fix slow accounts / statuses using emojis lookups (#2056)

* update DeleteEmoji to use faster relational tables for status / account finding

Signed-off-by: kim <grufwub@gmail.com>

* update Get{Accounts,Statuses}UsingEmoji() to also use relational tables

Signed-off-by: kim <grufwub@gmail.com>

* remove the now unneeded tags relation from newStatusQ()

Signed-off-by: kim <grufwub@gmail.com>

* fix table names

Signed-off-by: kim <grufwub@gmail.com>

* fix account and status selects using emojis

Signed-off-by: kim <grufwub@gmail.com>

---------

Signed-off-by: kim <grufwub@gmail.com>

3 files changed, 45 insertions, 92 deletions

diff --git a/internal/db/bundb/account.go b/internal/db/bundb/account.go
index e57c01a82..6a47418b7 100644
--- a/internal/db/bundb/account.go
+++ b/internal/db/bundb/account.go
@@ -468,24 +468,13 @@ func (a *accountDB) GetAccountCustomCSSByUsername(ctx context.Context, username
 func (a *accountDB) GetAccountsUsingEmoji(ctx context.Context, emojiID string) ([]*gtsmodel.Account, error) {
 	var accountIDs []string
 
-	// Create SELECT account query.
-	q := a.db.NewSelect().
-		Table("accounts").
-		Column("id")
-
-	// Append a WHERE LIKE clause to the query
-	// that checks the `emoji` column for any
-	// text containing this specific emoji ID.
-	//
-	// The reason we do this instead of doing a
-	// `WHERE ? IN (emojis)` is that the latter
-	// ends up being much MUCH slower, and the
-	// database stores this ID-array-column as
-	// text anyways, allowing a simple LIKE query.
-	q = whereLike(q, "emojis", emojiID)
-
-	// Execute the query, scanning destination into accountIDs.
-	if _, err := q.Exec(ctx, &accountIDs); err != nil {
+	// SELECT all accounts using this emoji,
+	// using a relational table for improved perf.
+	if _, err := a.db.NewSelect().
+		Table("account_to_emojis").
+		Column("account_id").
+		Where("? = ?", bun.Ident("emoji_id"), emojiID).
+		Exec(ctx, &accountIDs); err != nil {
 		return nil, a.db.ProcessError(err)
 	}
 
diff --git a/internal/db/bundb/emoji.go b/internal/db/bundb/emoji.go
index 04f22b6e9..e675339a2 100644
--- a/internal/db/bundb/emoji.go
+++ b/internal/db/bundb/emoji.go
@@ -106,47 +106,36 @@ func (e *emojiDB) DeleteEmojiByID(ctx context.Context, id string) error {
 	}
 
 	return e.db.RunInTx(ctx, func(tx bun.Tx) error {
-		// delete links between this emoji and any statuses that use it
-		// TODO: remove when we delete this table
-		if _, err := tx.
-			NewDelete().
-			TableExpr("? AS ?", bun.Ident("status_to_emojis"), bun.Ident("status_to_emoji")).
-			Where("? = ?", bun.Ident("status_to_emoji.emoji_id"), id).
-			Exec(ctx); err != nil {
-			return err
-		}
-
-		// delete links between this emoji and any accounts that use it
-		// TODO: remove when we delete this table
-		if _, err := tx.
-			NewDelete().
-			TableExpr("? AS ?", bun.Ident("account_to_emojis"), bun.Ident("account_to_emoji")).
-			Where("? = ?", bun.Ident("account_to_emoji.emoji_id"), id).
-			Exec(ctx); err != nil {
+		// Delete relational links between this emoji
+		// and any statuses using it, returning the
+		// status IDs so we can later update them.
+		if _, err := tx.NewDelete().
+			Table("status_to_emojis").
+			Where("? = ?", bun.Ident("emoji_id"), id).
+			Returning("status_id").
+			Exec(ctx, &statusIDs); err != nil {
 			return err
 		}
 
-		// Prepare a SELECT query with a WHERE LIKE
-		// that checks the `emoji` column for any
-		// text containing this specific emoji ID.
-		//
-		// (see GetStatusesUsingEmoji() for details.)
-		aq := tx.NewSelect().Table("accounts").Column("id")
-		aq = whereLike(aq, "emojis", id)
-
-		// Select all accounts using this emoji into accountIDss.
-		if _, err := aq.Exec(ctx, &accountIDs); err != nil {
+		// Delete relational links between this emoji
+		// and any accounts using it, returning the
+		// account IDs so we can later update them.
+		if _, err := tx.NewDelete().
+			Table("account_to_emojis").
+			Where("? = ?", bun.Ident("emoji_id"), id).
+			Returning("account_id").
+			Exec(ctx, &accountIDs); err != nil {
 			return err
 		}
 
-		for _, id := range accountIDs {
+		for _, id := range statusIDs {
 			var emojiIDs []string
 
-			// Select account with ID.
+			// Select statuses with ID.
 			if _, err := tx.NewSelect().
-				Table("accounts").
+				Table("statuses").
 				Column("emojis").
-				Where("id = ?", id).
+				Where("? = ?", bun.Ident("id"), id).
 				Exec(ctx); err != nil &&
 				err != sql.ErrNoRows {
 				return err
@@ -155,10 +144,10 @@ func (e *emojiDB) DeleteEmojiByID(ctx context.Context, id string) error {
 			// Drop ID from account emojis.
 			emojiIDs = dropID(emojiIDs, id)
 
-			// Update account emoji IDs.
+			// Update status emoji IDs.
 			if _, err := tx.NewUpdate().
-				Table("accounts").
-				Where("id = ?", id).
+				Table("statuses").
+				Where("? = ?", bun.Ident("id"), id).
 				Set("emojis = ?", emojiIDs).
 				Exec(ctx); err != nil &&
 				err != sql.ErrNoRows {
@@ -166,27 +155,14 @@ func (e *emojiDB) DeleteEmojiByID(ctx context.Context, id string) error {
 			}
 		}
 
-		// Prepare a SELECT query with a WHERE LIKE
-		// that checks the `emoji` column for any
-		// text containing this specific emoji ID.
-		//
-		// (see GetStatusesUsingEmoji() for details.)
-		sq := tx.NewSelect().Table("statuses").Column("id")
-		sq = whereLike(sq, "emojis", id)
-
-		// Select all statuses using this emoji into statusIDs.
-		if _, err := sq.Exec(ctx, &statusIDs); err != nil {
-			return err
-		}
-
-		for _, id := range statusIDs {
+		for _, id := range accountIDs {
 			var emojiIDs []string
 
-			// Select statuses with ID.
+			// Select account with ID.
 			if _, err := tx.NewSelect().
-				Table("statuses").
+				Table("accounts").
 				Column("emojis").
-				Where("id = ?", id).
+				Where("? = ?", bun.Ident("id"), id).
 				Exec(ctx); err != nil &&
 				err != sql.ErrNoRows {
 				return err
@@ -195,10 +171,10 @@ func (e *emojiDB) DeleteEmojiByID(ctx context.Context, id string) error {
 			// Drop ID from account emojis.
 			emojiIDs = dropID(emojiIDs, id)
 
-			// Update status emoji IDs.
+			// Update account emoji IDs.
 			if _, err := tx.NewUpdate().
-				Table("statuses").
-				Where("id = ?", id).
+				Table("accounts").
+				Where("? = ?", bun.Ident("id"), id).
 				Set("emojis = ?", emojiIDs).
 				Exec(ctx); err != nil &&
 				err != sql.ErrNoRows {
@@ -209,7 +185,7 @@ func (e *emojiDB) DeleteEmojiByID(ctx context.Context, id string) error {
 		// Delete emoji from database.
 		if _, err := tx.NewDelete().
 			Table("emojis").
-			Where("id = ?", id).
+			Where("? = ?", bun.Ident("id"), id).
 			Exec(ctx); err != nil {
 			return err
 		}
diff --git a/internal/db/bundb/status.go b/internal/db/bundb/status.go
index 0fef01736..c34074dd6 100644
--- a/internal/db/bundb/status.go
+++ b/internal/db/bundb/status.go
@@ -43,7 +43,6 @@ func (s *statusDB) newStatusQ(status interface{}) *bun.SelectQuery {
 	return s.db.
 		NewSelect().
 		Model(status).
-		Relation("Tags").
 		Relation("CreatedWithApplication")
 }
 
@@ -440,24 +439,13 @@ func (s *statusDB) DeleteStatusByID(ctx context.Context, id string) error {
 func (s *statusDB) GetStatusesUsingEmoji(ctx context.Context, emojiID string) ([]*gtsmodel.Status, error) {
 	var statusIDs []string
 
-	// Create SELECT status query.
-	q := s.db.NewSelect().
-		Table("statuses").
-		Column("id")
-
-	// Append a WHERE LIKE clause to the query
-	// that checks the `emoji` column for any
-	// text containing this specific emoji ID.
-	//
-	// The reason we do this instead of doing a
-	// `WHERE ? IN (emojis)` is that the latter
-	// ends up being much MUCH slower, and the
-	// database stores this ID-array-column as
-	// text anyways, allowing a simple LIKE query.
-	q = whereLike(q, "emojis", emojiID)
-
-	// Execute the query, scanning destination into statusIDs.
-	if _, err := q.Exec(ctx, &statusIDs); err != nil {
+	// SELECT all statuses using this emoji,
+	// using a relational table for improved perf.
+	if _, err := s.db.NewSelect().
+		Table("status_to_emojis").
+		Column("status_id").
+		Where("? = ?", bun.Ident("emoji_id"), emojiID).
+		Exec(ctx, &statusIDs); err != nil {
 		return nil, s.db.ProcessError(err)
 	}