I'll take a crack at this. Note, that I've just listed fields that are necessary. If you have additional data you want to store for in those tables, feel free to add them (so long as they are normalized).
While you did state that you would use a different database for each region, I designed this so that they could all work in one database. Right now you may have only 5 regions, but this offers the flexibility to easily add more:
table: region
regionID varchar(2)
regionName varchar(50)
Defines data for each realm. I went with a surrogate key (realmID) because it's possible that realmName might not be unique. This assumes that each realm can only belong to one region (regionID needs to be a foreign key to regionID on the region table):
table: realm
realmID int
regionID varchar(2)
realmName varchar(50)
Defines data for each user (seller/buyer). I decided that it would be better if it had a surrogate key (sellerID) because seller/buyers first/lastname combinations may not be totally unique. Note, you may also want an address table to store multiple:
table: user
userID int
userFirstName varchar(20)
userLastName varchar(20)
userEmail varchar(100)
Data for each auction. AuctionID would be a auto-generated ID and the Primary Key. You would also want foreign keys on realmID (realmID on realm table) and sellerID (userID on user table):
table: auction
auctionID int
realmID int
sellerID int
auctionPrice decimal
auctionStart datetime
Data for each auction that has been completed with a foreign key on buyerID (userID on user table):
table: auctionHistory
auctionID int
buyerID int
finalPrice decimal
auctionFinished datetime
I don't think that you'd need a seperate table for "Scan". That would be a simple query of rows in the auction table that do not have matching rows in the auctionHistory table. Hopefully, this will help you get started. If I missed/misread anything, please let me know.
You say that the hierarchy gets modified. Presumably while this operation is running, there is some amount of blocking which is taking place then?
Even if the hierarchy is changing, are the roots for items changing?
Have you looked at the time it would take to just make the mapping table from root to item and index it?
I would like to see the execution plan to see what is happening - the CTE should get spooled, but as a manually materialized and indexed table it might perform better in the later steps.
Even with heavy activity, it would seem to me that someone has to be blocked if DML operations are changing data which this process is reading.
So I'd strongly consider taking a snapshot of the hierarchy.
In addition, you have a number of other INNER JOINs - you should review whether it is, in fact, the CTEs at all and whether there are any indexes which are missing to make those joins effective. The execution plan should tell you that.
You appear to have quite a few things in the WHERE clause which might help reduce some operations (and determine which indexes might be the best)), but it's hard to tell without looking at the execution plan or the indexes.
Best Answer
Generally, no. A few tangentially related things to note though:
You could use a symlink to move the
/<datadir>/db2/
directory to another storage/block device. Or you could use theCREATE TABLE ... DATADIR=/../
option to move only the support_chat table to another storage/block device. That could make a big difference, assuming that you're frequently disk bound.With MySQL 5.6, using multiple databases/schemas can help to improve slave throughput (assuming you're using replication) because the parallelization was at the schema level:
Replication Slave Options and Variables
--slave-parallel-workers
So with three databases/schemas, you would benefit from setting
slave-parallel-workers=3
.For more info on the multi-threaded slave work in MySQL 5.6:
MySQL Replication High Performance: Multi-Threaded Slaves and Group Commit (pdf)
Just FYI, the multi-threaded slave behavior has been greatly improved in MySQL 5.7 too, the biggest example being that you can now instead use (Lamport) logical clock based parallelization:
Replication Slave Options and Variables
--slave-parallel-type