MySQL index design with table partitioning

问题:

I have 2 MySQL tables with the following schemas for a web site that's kinda like a magazine.

Article (articleId int auto increment ,
         title varchar(100),
         titleHash guid -- a hash of the title
         articleText varchar(4000)
         userId int)

User (userId int autoincrement
      userName varchar(30)
      email etc...)

The most important query is;

select title,articleText,userName,email 
from Article inner join user
on article.userId = user.UserId
where titleHash = <some hash>

I am thinking of using the articleId and titleHash columns together as a clustered primary y for the Article table. And userId and userName as a primary key for the user table. As the searches will be based on titlehash and userName columns.

Also titlehash and userName are unqiue by design and will not change normally.

The articleId and userid columns are not business keys and are not visible to the application, so they'll only be used for joins.

I'm going to use mysql table partitioning on the titlehash column so the selects will be faster as the db will be able to use partition elimination based on that column.

I'm using innoDB as the storage engine;

So here are my questions;

1. Do I need to create another index on the titlehash column as the primary key (articleId,titlehash) is not good for the searches on the titlehash column as it is the second column on the primary key ?

2. What are the problems with this design ?

I need the selects to be very fast and expects the tables to have millions of rows and please note that the int Id columns are not visible to the business layer and can never be used to find a record

I'm from a sql server background and going to use mysql as using the partitioning on sql server will cost me a fortune as it is only available in the Enterprise edition.

So DB gurus, please help me; Many thanks.

回答1:

As written, your "most important query" doesn't actually appear to involve the User table at all. If there isn't just something missing, the best way to speed this up will be to get the User table out of the picture and create an index on titleHash. Boom, done.

If there's another condition on that query, we'll need to know what it is to give any more specific advice.

Given your changes, all that should be necessary as far as keys should be:

• On Article:
  • PRIMARY KEY (articleId) (no additional columns, don't try to be fancy)
  • KEY (userId)
  • UNIQUE KEY (titleHash)
• On User:
  • PRIMARY KEY (userId)

Don't try to get fancy with composite primary keys. Primary keys which just consist of an autoincrementing integer are handled more efficiently by InnoDB, as the key can be used internally as a row ID. In effect, you get one integer primary key "for free".

Above all else, test with real data and look at the results from EXPLAINing your query.