0

Any way to combine the following queries, or some of them? My goal is to achieve faster performance with fewer inserts. However I don't know how to handle the ON DUPLICATE KEY UPDATE new_table.pages=VALUES(pages) where I will insert more than 2 variables.

mysql_query("INSERT INTO new_table (hash, pages)
SELECT hash, COUNT( id ) AS pages
FROM behaviour GROUP BY hash
ON DUPLICATE KEY UPDATE new_table.pages=VALUES(pages)");


mysql_query("INSERT INTO new_table (hash, visits)
SELECT hash, visits from audience
ON DUPLICATE KEY UPDATE new_table.visits=audience.visits");


mysql_query("INSERT INTO new_table (hash, first_visit)
SELECT hash, timestamp from audience
ON DUPLICATE KEY UPDATE new_table.first_visit=audience.timestamp");


mysql_query("INSERT INTO new_table (hash, last_visit)
SELECT hash, max(timestamp) from behaviour
group by hash
ON DUPLICATE KEY UPDATE new_table.last_visit=VALUES(last_visit)");


mysql_query("INSERT INTO new_table (hash, goals)
SELECT alerts_data_hash, COUNT( * ) AS goals
FROM alerts_data
WHERE alerts_data_status =  'goal'
GROUP BY alerts_data_hash
ON DUPLICATE KEY UPDATE new_table.goals=VALUES(goals)");
EnexoOnoma
  • 8,454
  • 18
  • 94
  • 179
  • you could use a multi-query by switching over to the mysqli_ api; that's one option and that api could be slightly faster than mysql_. Might not solve the question but will help reduce your code a tad. – Funk Forty Niner Jan 04 '17 at 16:30
  • Plain `mysql_` has been deprecated for some time anyway. You should be jumping to `mysqli_` family of functions – yivi Jan 04 '17 at 16:34
  • 1
    Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) – RiggsFolly Jan 04 '17 at 16:35
  • @RiggsFolly good, because i am a dog person! – EnexoOnoma Jan 04 '17 at 22:47

1 Answers1

0

I believe this will combine the first 4 queries

INSERT INTO new_table (hash, pages, visits, first_visit, last_visit, goals)
SELECT A.hash, COUNT(B.id), A.visits, A.timestamp, MAX(B.timestamp), 0
FROM audience A
LEFT OUTER JOIN behaviour B ON B.hash = A.hash
GROUP BY A.hash

I'm not sure if the last one can be a join or would have to be a union/separate query.

CptMisery
  • 612
  • 4
  • 15
  • what about the "duplicate key" line? – EnexoOnoma Jan 04 '17 at 18:53
  • Ah, forgot that bit. However you can, after the `GROUP BY`, add `ON DUPLICATE KEY UPDATE new_table.pages=VALUES(pages), new_table.visits=a.visits, etc`. Here is another post about updating multiple columns on a duplicate key insert: http://stackoverflow.com/questions/8991593/on-duplicate-key-update-multiple-columns – CptMisery Jan 04 '17 at 19:10