There are two main queries I'm tying to improve, one question is much more conceptual and learning about databases and another is much more straightforward.
Both cases deal with the same table
It has 2 relevant columns as far as the question is concerned, they make up the primary key of the table. Each of the elements of the primary key is hypothetically the key to another table n their own right. The first row is an int (this is an item) an the second row is a
VARCHAR (this is a user) limited to 200 characters.
The first issue comes in trying to find an intersection of users. I am looking to find an intersection of users that share a number of items, 5 in the example
SELECT userid FROM (SELECT b.userid, b.showid FROM ratings AS A JOIN ratings AS B ON A.userid LIKE :user AND B.userid NOT LIKE :user AND A.showid LIKE B.showid) AS C GROUP BY userid HAVING Count(*) > 4
On a fast run this takes up about 2/3 of the runtime of my program (appearing twice, one nested in another query taking little longer than this one). I've tried looking at it but I don't see any obvious way to restructure the query to save time, but queries operating over many more rows are coming up with runtimes of less than 1 second while this takes 2 seconds.
The second issue is more conceptual. As mentioned before I have a composite primary key. I've found elsewhere that with
InnoDB tables this can make inserts take up a lot more time. This is exactly whats happening with an
INSERT ... UPDATE ON DUPLICATE KEY query that I'm running. What is the best way to go about reducing this slowdown.
This is what the insert looks like, it only runs if the show has been rated
INSERT INTO shows ( showid, showname, showimage ) VALUES ( :id, :name, :image ) on duplicate KEY UPDATE showimage=:image, showname= :name; INSERT INTO ratings ( userid, showid, rating ) VALUES ( :user, :show, :rating ) on duplicate KEY UPDATE rating=:rating
CREATE TABLE "shows" ( "ShowId" int(11) NOT NULL, "ShowName" varchar(400) NOT NULL, "ShowImage" varchar(400) NOT NULL, "ShowAgreement" int(11) NOT NULL DEFAULT "0", PRIMARY KEY ("ShowId") ) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE "ratings" ( "UserId" varchar(200) NOT NULL, "ShowId" int(11) NOT NULL, "Rating" int(11) NOT NULL, PRIMARY KEY ("UserId","ShowId"), KEY "ShowId" ("ShowId"), CONSTRAINT "ratings_ibfk_1" FOREIGN KEY ("ShowId") REFERENCES "shows" ("ShowId") ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1
For a single test case the INSERT takes an additional 8 seconds over the UPDATE for 160 entries.
- Should I simply run the conditional part of the query codeside and removing the primary key.
- Should I drop the primary key and replace it with a unique constraint?
- Should I create a new table for the UserId that creates a
INTidentity for userId's and give all existing tables the
INTversion of the id rather than the
- Should I do something else entirely that hadn't occurred to me?