Page MenuHomePhabricator
Differential D6924 Diff 23875 keyserver/src/database/migration-config.js

Changeset View

Standalone View

View Options

keyserver/src/database/migration-config.js

Show First 20 Lines • Show All 210 Lines • ▼ Show 20 Lines async () => {
await dbQuery(SQL` await dbQuery(SQL`
ALTER TABLE threads ALTER TABLE threads
ADD COLUMN IF NOT EXISTS avatar varchar(191) ADD COLUMN IF NOT EXISTS avatar varchar(191)
COLLATE utf8mb4_bin COLLATE utf8mb4_bin
DEFAULT NULL; DEFAULT NULL;
`); `);
}, },
], ],
[
21,
async () => {
await dbQuery(SQL`
ALTER TABLE messages
ADD COLUMN IF NOT EXISTS pinned tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
ADD COLUMN IF NOT EXISTS pin_time bigint(20) DEFAULT NULL,
ADD INDEX IF NOT EXISTS pinned (pinned);
tomekUnsubmitted
Not Done
Inline Actions

What is the purpose of this index? When are we going to use it?

If we have a plan to use it, should we also want to include a thread id in it?

tomek: What is the purpose of this index? When are we going to use it? If we have a plan to use it…
rohanAuthorUnsubmitted
Done
Inline Actions

I'ved added an index on pinned since when we fetch all message pins for a thread, we'd be querying on message.pinned = 1, so I thought it'd be good to have indexed. threadID is already indexed it seems like.

The query is specifically in D7110.

rohan: I'ved added an index on `pinned` since when we fetch all message pins for a thread, we'd be…
tomekUnsubmitted
Not Done
Inline Actions

Have you tested that both indexes are used in this query (using EXPLAIN)? I would be surprised if the engine uses both of them - more likely case is that the first index is used and then a scan is performed. The solution to that is using a compound index - consisting of two columns.

tomek: Have you tested that both indexes are used in this query (using `EXPLAIN`)? I would be…
rohanAuthorUnsubmitted
Done
Inline Actions

Ah I suspect you're right, it looks like it chooses one of the indexes.

I can modify the ALTER TABLE to create a compound index, and the migration as well

rohan: Ah I suspect you're right, it looks like it chooses one of the indexes. I can modify the…
ashoatUnsubmitted
Not Done
Inline Actions

Yeah I think you should do this (no need to wait on @tomek to reply)

ashoat: Yeah I think you should do this (no need to wait on @tomek to reply)
`);
},
],
]); ]);
const newDatabaseVersion: number = Math.max(...migrations.keys()); const newDatabaseVersion: number = Math.max(...migrations.keys());
async function writeJSONToFile(data: any, filePath: string): Promise<void> { async function writeJSONToFile(data: any, filePath: string): Promise<void> {
console.warn(`updating ${filePath} to ${JSON.stringify(data)}`); console.warn(`updating ${filePath} to ${JSON.stringify(data)}`);
const fileHandle = await fs.promises.open(filePath, 'w'); const fileHandle = await fs.promises.open(filePath, 'w');
await fileHandle.writeFile(JSON.stringify(data, null, ' '), 'utf8'); await fileHandle.writeFile(JSON.stringify(data, null, ' '), 'utf8');
await fileHandle.close(); await fileHandle.close();
▲ Show 20 Lines • Show All 89 Lines • Show Last 20 Lines