Kysely•9mo ago

Subquery on same table

I'm experimenting with how to improve the performance of some queries against a PlanetScale MySQL DB. I have a fulltext index on a field in a table, then some other indexes on various other fields. MySQL can't use two indexes for a query so I'd like to try including the section of the query that uses the fulltext column in a subquery but I'm struggling with the syntax The primary key of the table is activityId. Something like

SELECT * FROM activities
WHERE someCol = '123'
AND someOtherCol = 'abc
AND activityId IN 
(
  SELECT activityId FROM activities
  MATCH(bigTextField) AGAINST ("*some text*" IN BOOLEAN MODE)
)

SELECT * FROM activities
WHERE someCol = '123'
AND someOtherCol = 'abc
AND activityId IN 
(
  SELECT activityId FROM activities
  MATCH(bigTextField) AGAINST ("*some text*" IN BOOLEAN MODE)
)

but I'm struggling with the Kysely syntax to achieve this, can anyone please point me in the right description?

3 Replies

Ross•9mo ago

I've managed to achieve it by using the sql tag like this:

query.where(
            'activityId',
            'in',
            sql`(SELECT activityId FROM activities WHERE MATCH(bigTextField) AGAINST (${value} IN BOOLEAN MODE))`,
          );

query.where(
            'activityId',
            'in',
            sql`(SELECT activityId FROM activities WHERE MATCH(bigTextField) AGAINST (${value} IN BOOLEAN MODE))`,
          );

but I reckon there must be a better way...

Unknown User•9mo ago

Message Not Public

Ross•9mo ago

Thanks, I'll have a play with that

Gaming

Programming

Subquery on same table