Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ## I'm doing a lot of these, where X is an integer
- INSERT INTO deviations(pivot_id, deviant_id, value)
- SELECT
- X as pivot_id,
- ratings2.content_id as deviant_id,
- AVG(ratings.rate - ratings2.rate) as value
- FROM ratings
- INNER JOIN ratings as ratings2
- ON ratings.viewer_id = ratings2.viewer_id
- AND ratings.viewer_type = ratings2.viewer_type
- AND ratings.content_id = X
- AND ratings.content_id < ratings2.content_id
- GROUP BY deviant_id
- ## However I also tried doing a single query for all those IDs:
- INSERT INTO deviations(pivot_id, deviant_id, value)
- SELECT
- ratings.content_id as pivot_id,
- ratings2.content_id as deviant_id,
- AVG(ratings.rate - ratings2.rate) as value
- FROM ratings
- INNER JOIN ratings as ratings2
- ON ratings.viewer_id = ratings2.viewer_id
- AND ratings.viewer_type = ratings2.viewer_type
- AND ratings.content_id < ratings2.content_id
- GROUP BY pivot_id, deviant_id
- ## Results in seconds
- | MANY QUERIES | ONE BIG QUERY |
- Default my.cnf values: | 194 | 267
- 8MB for max_heap_table_size
- and tmp_table_size: | 367 | 298 |
- 265MB for max_heap_table_size
- and tmp_table_size: 389 seconds | 358 | 78 |
- How come using more memory is worse???
Add Comment
Please, Sign In to add comment