MySQL column type to boost performance

database-designmyisamMySQLoptimizationperformance

I'll have a table where:

  • userid (link to user's id in users table)
  • foldername (a varchar, max. 260 char)
  • entry (varchar, max. 1024)
  • data (varchar, max. 20kb)

Single user will have several foldername and entries.

The point is, queries will never ever search anything inside entry or data. Queries ONLY will be like

select * from data where userid  = 1 and foldername = 'first';

That's it.

So I thought about two options:

  • a. Instead of entry and data columns, storing them inside HDD and only storing filename in DB, so when query returns, PHP app. will read filename, then read the file and send it to user in HTTP response.
  • b. Store them in DB as I said above

But I'll never run queries on them, so no sorting/indexing will be needed and no full text search will run ever.

QUESTIONS

  • What's the best possible optimization here?
  • How can I define a column as a "not-really-a-database-entry" type in MySQL, or is file better?

Best Answer

Having a VARCHAR as a part of an index would be cumbersome to manage although doable.

I would design the tables as follows

CREATE TABLE folderkeys
(
    id INT NOT NULL AUTO_INCREMENT,
    userid INT NOT NULL,
    foldername VARCHAR(260),
    PRIMARY KEY (id),
    UNIQUE KEY (userid,foldername)
) ENGINE=MyISAM;
CREATE TABLE datatable
(
    id INT NOT NULL AUTO_INCREMENT,
    folderkey_id INT NOT NULL,
    filename VARCHAR(1024),
    PRIMARY KEY (id),
    KEY (folderkey_id)
) ENGINE=MyISAM;

This will make the datatables indexes as character-compacted as possible

This will make it as easy doing queries like

INSERT IGNORE INTO folderkeys (userid,foldername) VALUES (1,'first');
SELECT id INTO @fldrkey FROM folderkeys WHERE userid=1 AND foldername='first';
INSERT INTO datatable (folderkey_id,filename) VALUES (@fldrkey,'...');

If you want the filename from userid 1 foldername 'first', you would do queries like this

SET @GivenUserID = 1;
SET @GivenFolder = 'first';
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID AND foldername=@GivenFolder) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

If you want filenames from all folders for userid 27, you would do queries like this

SET @GivenUserID = 27;
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

Give it a Try !!!

I would normally recommend using InnoDB. Since you have lots of BLOB data, I'd rather not spend time trying to tune your log buffer and log files sizes. I would go with MyISAM for now.

BTW I gave you the tables with Option A in mind

Related Question