Small starting clarification: the article you linked to on InnoDB text/blob storage is a little out of date with MySQL 5.5, and the barracuda row format. This article is more up to date.
On to your question:
In the schema you have, each row will be less than ~8K, so you can guarantee across both antelope and barracuda row formats that all data will be stored in-line - not requiring external/overflow pages.
If you were to require overflow pages, they are never de-duplicated (which is what I would probably describe your 'pooling' mechanism as). Even worse than they are never de-duplicated, they are never shared...
If you could have a record too big to fit inline (~8K limit), each text/blob that needs to be moved out will take a minimum of a 16K page to itself.
Your original query joins everything together then traverses 50000 rows into the joined data before presenting the next 100 rows. Try this
- Get the 100 keys from ProjectManagement2 upfront
- join everything afterwards
Here is my proposed query
SELECT i.id, i.`key`, i.title, i.description,
CONCAT(ru.firstName, ' ', ru.lastName) as `name`,
CONCAT(au.firstName, ' ', au.lastName) as `name`,
p.title, pc.title, pva.title, pvo.title, pvf.title, i.durationEstimate,
i.storyPoints, i.dueDate, isl.title, i.rejectionCount,
CONCAT(uc.firstName, ' ', uc.lastName) as `name`,
i.createdTimestamp, i.updatedTimestamp, it.title, isss.title
FROM
(
SELECT B.* FROM
(SELECT id FROM ProjectManagement2 LIMIT 50000, 100) A
INNER JOIN ProjectManagement2 B USING (id)
) i
INNER JOIN Users ru ON ru.id = i.reporterUserUsername
INNER JOIN Users au ON au.id = i.assignedUserUsername
INNER JOIN Projects p ON p.id = i.projectTitle
INNER JOIN ProjectComponents pc ON pc.id = i.projectComponentTitle
INNER JOIN ProjectVersions pva ON pva.id = i.affectedProjectVersionTitle
INNER JOIN ProjectVersions pvo ON pvo.id = i.originalFixedProjectVersionTitle
INNER JOIN ProjectVersions pvf ON pvf.id = i.fixedProjectVersionTitle
INNER JOIN IssueSecurityLevels isl ON isl.id = i.issueSecurityLevelId
INNER JOIN IssueTypes it ON it.id = i.issueTypeId
INNER JOIN IssueStatuses isss ON isss.id = i.issueStatusId
INNER JOIN Users uc ON uc.id = i.creatorUserUsername
;
Give it a Try !!!
Best Answer
One thing that has to be taken into consideration is how MySQL uses buffers for its major storage engines: InnoDB and MyISAM.
What lies cached in memory differs greatly between these storage engines.
InnoDB caches both data and index pages. They are loaded into the InnoDB Buffer Pool, which is sized by innodb_buffer_pool_size.
MyISAM caches only index pages and they are loaded into the Key Cache (Key Buffer), which is sized by key_buffer_size.
You must use information_schema.tables to get the data and index sizes occupied on disk in order to size the InnoDB Buffer Pool and MyISAM Key Cache correctly.
Depending on on how much data you have and how much time you will allow, you can warm the caches as follows:
For every table TableT
By doing this you guarantee that every data and index page gets read at least once. They will sit in the cache. This concept is practiced, in part and in principle, by Percona. Percona built this concept into mk-slave-prefetch. What this program does is
This forces the slave to have 99.99% of the data needed by the slave to process the SQL quickly. This also makes the slave prepared in the event you manually failover to the slave and promote it to a master WHOSE CACHES ARE JUST ABOUT THE SAME AS THE MASTER YOU FAILED OVER FROM.
CONCLUSION
Nothing beats having caches ready, willing, and able for you to use in an environment of heavy INSERTS, UPDATEs, and DELETEs.
Give it a Try !!!
CAVEAT
With the birth of products like memcached, some have gotten away from the need to perform proper tuning of MySQL. Granted, many sites benefit from the boost in data retrieval provided by controlling the caching behavior of data as developers have quickly seen with memcached. Many other sites, just by switching storages engines or correctly configuring MySQL, have realized the same performance benefits. Before giving up on the database and strictly using it as a repository, make the most of the your database. Follow through on the due diligence and you might be pleasantly surprised what MySQL will do for you.