Kysely•3y 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)
)

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?

RossOP•9/5/23, 8:23 PM

I've managed to achieve it by using the sqlsql 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...

RossOP•9/6/23, 7:16 AM

Thanks, I'll have a play with that

Subquery on same table

Similar Threads

Similar Threads

Similar Threads