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;
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 ?
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.
Copyright Notice:Content Author:「Amila」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/6317593/mysql-index-design-with-table-partitioning