Timeline for Optimizing Queries HAVING COUNT and INSERT...ON DUPLICATE KEY UPDATE
Current License: CC BY-SA 3.0
22 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 8, 2015 at 16:33 | vote | accept | pafk | ||
| Jul 8, 2015 at 16:31 | history | edited | pafk | CC BY-SA 3.0 |
deleted 95 characters in body
|
| Jul 8, 2015 at 15:17 | comment | added | Rick James |
Every table "should" have a PRIMARY KEY, don't change it to UNIQUE. If you will have thousands of ratings; it would be good to "normalize" UserId.
|
|
| Jul 8, 2015 at 12:49 | comment | added | pafk |
As you requested, the SHOW CREATE TABLE @RickJames
|
|
| Jul 8, 2015 at 12:46 | history | edited | pafk | CC BY-SA 3.0 |
added 448 characters in body
|
| Jul 8, 2015 at 4:31 | comment | added | Rick James |
Please provide SHOW CREATE TABLE for both tables.
|
|
| Jul 8, 2015 at 0:09 | history | edited | pafk | CC BY-SA 3.0 |
added 371 characters in body
|
| Jul 7, 2015 at 23:44 | history | edited | pafk | CC BY-SA 3.0 |
added 974 characters in body
|
| Jul 7, 2015 at 22:10 | answer | added | Lennart - Slava Ukraini | timeline score: 0 | |
| Jul 7, 2015 at 21:44 | comment | added | Lennart - Slava Ukraini | I noticed after my comment :-) I'll comment once the query is updated | |
| Jul 7, 2015 at 21:44 | history | edited | pafk | CC BY-SA 3.0 |
added 38 characters in body
|
| Jul 7, 2015 at 21:43 | comment | added | pafk | I fixed the join thanks to you and i'm about to upload the change | |
| Jul 7, 2015 at 21:41 | comment | added | Lennart - Slava Ukraini |
So on average each user have 188 items. I don't understand what the query is supposed to do, assume A.userid LIKE :user matches one of those users, that's 188 rows. B.userid NOT LIKE :user then matches the remaining users, so the cardinality of the join would be somewhere in the ballpark of a miljon rows. Are you sure nothing is missing in the join predicate?
|
|
| Jul 7, 2015 at 21:40 | comment | added | pafk | Thanks for pointing out the join condition, I just realized I forgot to make sure that this is only happening for items both users have rated, this is giving me a slight speedup | |
| Jul 7, 2015 at 20:57 | history | edited | pafk | CC BY-SA 3.0 |
added 23 characters in body
|
| Jul 7, 2015 at 20:55 | comment | added | pafk | My service is telling me cardinality for UserId is 37 and 6958 for ItemId | |
| Jul 7, 2015 at 20:50 | comment | added | Lennart - Slava Ukraini | Also, the closing parenthesis for the sub-select is missing | |
| Jul 7, 2015 at 20:42 | comment | added | Lennart - Slava Ukraini | What is the cardinality of ratings? The join condition looks fairly dangerous, is something missing there (itemid for example)? | |
| S Jul 7, 2015 at 20:20 | history | suggested | oNare | CC BY-SA 3.0 |
Improved formatting
|
| Jul 7, 2015 at 20:16 | review | Suggested edits | |||
| S Jul 7, 2015 at 20:20 | |||||
| Jul 7, 2015 at 19:54 | review | First posts | |||
| Jul 7, 2015 at 19:55 | |||||
| Jul 7, 2015 at 19:47 | history | asked | pafk | CC BY-SA 3.0 |