CODEWITHSUNDEEP
mysqlperformance
dotwin
dotwin

Reputation: 1332

Optimize Lookup Performance, MySQL

I am relatively new to databasing; so sorry if this is 101 knowledge that I haven't soaked up yet. I googled but found no satisfying answer.

I am writing a program that walks through ~20mio files and puts their file names all in one table (I have strong preferences of keeping it in one table for future use). Each file_name contains a BIGINT id (among other Ascii characters). There are only about 20 file_names for each BIGINT id. My question now: Can I leverage this name structure for better lookup performance?

My table structure so far:

CREATE TABLE IF NOT EXISTS files
        (file_name VARCHAR(40) CHARACTER SET ascii NOT NULL PRIMARY KEY,"
        id BIGINT UNSIGNED,
        ...)

And my lookup statements are just:

WHERE file_name = '...'

Is it, for example, better to index id and then look up file_name and id?

Thanks a bunch in advance!

Upvotes: 1

Views: 80

Answers (2)

cristian v
cristian v

Reputation: 1063

I understand that you can have 20 different filenames for each ID so ID is not unique. Then you can't use the ID for the Primary Key. The only option is to use file_name as the Primary Key if you know that the file_name is unique. This would give you the max performance if you look for a specific file_name. If you also need to look for all the files with a specific ID you must create a Not Unique Index on the ID field.

Upvotes: 2

luksch
luksch

Reputation: 11712

The usual table design would be to let id be the PRIMARY KEY. If you want to query for file_name as well , an index on that column may be the right choice.

Upvotes: 1

Related Questions