My goal
I want to record data for a market basket analysis (https://en.wikipedia.org/wiki/Affinity_analysis) on computer software and query this this data frequently.
Data description
One user may own one item and use it for a certain time. Usage is saved as an integer in minutes. There is a distinction between usage_forever and usage_frequent. usage_frequent gives insights about the usage up to one week week in the past for one product. useage_forever with a zero indicates that the user bought the product but never used it. I used MySQL as the database mangement system.
SQL fiddle link with sample data: http://sqlfiddle.com/#!9/92d4bc
ownership table
+----------------------+-------------------+-------------+------------------------+------------------------+
| userID | productID | modified | usage_forever | usage_recent |
+----------------------+-------------------+-------------+------------------------+------------------------+
| bigint(20)unsigned#1 | int(10)unsigned#1 | timestamp#1 | mediumint(8)unsigned#1 | mediumint(8)unsigned#1 |
+----------------------+-------------------+-------------+------------------------+------------------------+
CREATE TABLE product(
productID INT UNSIGNED NOT NULL PRIMARY KEY,
product_name VARCHAR(191)
) ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE product_user(
userID BIGINT UNSIGNED NOT NULL PRIMARY KEY,
display_name VARCHAR(32) NOT NULL,
products_in_account SMALLINT NOT NULL DEFAULT 0 -- this is a derived value, denormalized for convenience
) ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE ownership (
userID BIGINT UNSIGNED NOT NULL,
productID INT UNSIGNED NOT NULL,
modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
usage_forever MEDIUMINT UNSIGNED NULL,
usage_recent MEDIUMINT UNSIGNED NULL,
PRIMARY KEY(userID, productID),
UNIQUE INDEX ux_ownership_productID_userID (productID, userID),
CONSTRAINT fk_ownership_productUser_userID
FOREIGN KEY (userID)
REFERENCES product_user(userID)
ON UPDATE CASCADE,
CONSTRAINT fk_ownership_product_productID
FOREIGN KEY (productID)
REFERENCES product(productID)
ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
The problem
The last time I stored this data there were huge performance issues. My product table contained 35,000 items and my user table 35,000 rows. This concluded in 1.4 million entries in my ownership table. Users own at most 11,000+ items but most of the users own in the range between 100 to 1000 items. Queries to this table were painfully slow so that I sliced it down to a tiny selection in order to perform my analysis.
Use cases. Ordered by most important task first
- Frequent select queries to specific users or specific items. This is the most important use case.
- update queries to usages to 50,000 users a day
- insertion queries of new users up to 1,000 times a day
- I want to be able to perform market basket analysis by user, group of users, products, or group of products
- very rare delete queries
My solution
My new proposal for this table: Split it into ownership_forever and ownership_recent:
ownership_forever, PRIMARY KEY(userID, proudct ID)
+----------------------+-------------------+------------------------+
| userID | productID | usage_forever |
+----------------------+-------------------+------------------------+
| bigint(20)unsigned#1 | int(10)unsigned#1 | mediumint(8)unsigned#1 |
+----------------------+-------------------+------------------------+
ownership_recent, PRIMARY KEY(userID, proudctID); PRIMARY KEY for archive(userID, proudctID, modified)
+----------------------+-------------------+-------------+------------------------+
| userID | productID | modified | usage_recent |
+----------------------+-------------------+-------------+------------------------+
| bigint(20)unsigned#1 | int(10)unsigned#1 | timestamp#1 | mediumint(8)unsigned#1 |
+----------------------+-------------------+-------------+------------------------+
This way many empty rows are omitted because many users never used their product. The fact however remains that these tables will become huge:
- I will have to work with at least 300,000 users. If I extrapolate
based on my sample my new
ownership_forevertable will contain 12 million rows. - In the future I want this table to scale up to 10 million users. If I extrapolate based on my sample
ownership_foreverwill contain 400 million rows. - I plan to periodically dump
ownership_recentinto a partitioned archive table.
Questions
I am not experienced enough to estimate if this is a feasible solution. Can you assist in answering the following questions?
- Does this approach make sense? Are there alternatives that I have missed?
- Should I also use partitions for my non-archive tables?
- I could convert the bigint
userIDinto a smaller int(10). Does this have any effect on performance? - What do you propose in order to query these large tables in an efficent way?
- Can MySQL handle this "kind" of data? Did I make a big mistake in my first sample or why did I have such slow performance?
- How should I set up the indexes?
- Is this viable with a relational database on one machine, a distributed database or do I already need to set up a data warehouse?
- My new data is supplied in a JSON format. This hints that NoSQL / document-based databases might be applicable. I never worked with any of those. Can comment comment if I should look into those?
The JSON format:
{"reply": {
"products_owned": 89,
"products": [
{
"productID": 11111,
"usage_forever": 11
},
{
"productID": 222222,
"usage_forever": 0
},
{
"productID": 333333,
"usage_forever": 0,
"usage_recent" : 69
}]}}
FOREIGN KEYrelationships. Also, your table structures above are puzzling - why do you have three lines for each table instead of one? You could give us the output ofSHOW CREATE TABLE the_table\Gfor the relevant tables! – Vérace May 19 '18 at 11:51SELECTs. – Rick James May 30 '18 at 23:46