1

My Page has over 30mio Views per Day and i can't cache the Page (The data must always be up-to-date).

I have three MYSQL Queries which I would like to summarize. Is this possible?

SELECT * 
FROM  `meta_holdings` 
INNER JOIN coins ON coins.cmc_id = meta_holdings.cmc_id
WHERE  `userId` = $userId
LIMIT 0 , 999

SELECT * 
FROM  `meta_watchlist` 
INNER JOIN coins ON coins.cmc_id = meta_watchlist.cmc_id
WHERE  `userId` = $userId
LIMIT 0 , 999

SELECT * 
FROM  `coins` 
ORDER BY  `coins`.`rank` ASC 
LIMIT 0 , 30

Glad to have any help

Peter
  • 11,413
  • 31
  • 100
  • 152
  • Possible duplicate of [Multiple select statements in Single query](https://stackoverflow.com/questions/1775168/multiple-select-statements-in-single-query) – NanThiyagan Jan 06 '18 at 09:00
  • 2
    Doesn't look like a duplicate of that to me. Instead, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Jan 06 '18 at 09:11
  • 1
    30M hits a day is 350 hits/s on average, surely you can cache for 1 second to reduce the unique hits to 85K – JimL Jan 06 '18 at 09:13

3 Answers3

1

A single query returns a single data set. By seeing your three queries, it looks like you need three data sets. You cannot achieve the same functionalities by mearging the queries. So the way it looks should be okay unless you have changed your requirements. Alternatively you can look into MySQL's internal cache.

Md Monjur Ul Hasan
  • 1,705
  • 1
  • 13
  • 36
0

// Try this ..

SELECT * FROM  `meta_holdings` INNER JOIN coins ON coins.cmc_id = meta_holdings.cmc_id WHERE  `userId` = $userId LIMIT 0 , 999 
UNION
SELECT * FROM  `meta_watchlist` INNER JOIN coins ON coins.cmc_id = meta_watchlist.cmc_id WHERE  `userId` = $userId LIMIT 0 , 999
UNION
SELECT * FROM  `coins` ORDER BY  `coins`.`rank` ASC LIMIT 0 , 30
Nimesh Patel
  • 796
  • 1
  • 7
  • 23
0

Here is the query if you are trying to find the top 30 rankings of coins table data of along with meta_holdings,meta_watchlist details -

SELECT * FROM  coins 
INNER JOIN meta_holdings ON coins.cmc_id = meta_holdings.cmc_id
INNER JOIN meta_watchlist ON coins.cmc_id = meta_watchlist.cmc_id
WHERE  meta_holdings.userId = $userId and meta_watchlist.userId = $userId
ORDER BY  `coins`.`rank` ASC 
LIMIT 0 , 30
Madhuri
  • 73
  • 1
  • 1
  • 7