mtgjson / mtgsqlive

MTGJSON build scripts to generate alternative data formats
https://mtgjson.com
MIT License
54 stars 32 forks source link

Add indexes to table creation #94

Closed zqft9001 closed 1 year ago

zqft9001 commented 1 year ago

As discussed in https://discord.com/channels/224178957103136779/1127350404292812821 on the discord, adding uuid indexes after the create statements for tables will speed up queries on uuids and joins using them significantly.

A statement like the below added after a table creation (referencing the appropriate table) will add an index for the UUID column for that table. The index name can be any appropriate name, but using [tablename]uuid would help with consistency and prevent collisions. Formatting appears to be the same between mysql and sqlite for basic indexes.

CREATE INDEX cardIdentifiersuuid ON cardIdentifiers(UUID);
CREATE INDEX cardsuuid ON cards(UUID);

Further detail on mysql indexes: https://dev.mysql.com/doc/refman/8.0/en/create-index.html

splatterb0y commented 1 year ago

This broke the import, with the following error on MySQL 8.0:

ERROR 1170 (42000) at line 120: BLOB/TEXT column 'uuid' used in key specification without a key length

ZeldaZach commented 1 year ago

@splatterb0y I pushed a change, looks like it fixed mysql on my end. Sorry about that!