Optimize 3 MYSQL Queries to one

Question

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

Possible duplicate of Multiple select statements in Single query — NanThiyagan
– NanThiyagan, Commented Jan 6, 2018 at 9:00
Doesn't look like a duplicate of that to me. Instead, see meta.stackoverflow.com/questions/333952/… — Strawberry
– Strawberry, Commented Jan 6, 2018 at 9:11
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
– JimL, Commented Jan 6, 2018 at 9:13

Md Monjur Ul Hasan · Accepted Answer · 2018-01-06 10:07:26Z

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.

answered Jan 6, 2018 at 10:07

Md Monjur Ul Hasan

1,8011 gold badge16 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nimesh Patel · Accepted Answer · 2018-01-06 10:13:33Z

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

answered Jan 6, 2018 at 10:13

Nimesh Patel

8041 gold badge8 silver badges23 bronze badges

Comments

Madhuri · Accepted Answer · 2018-01-07 09:43:34Z

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

edited Jan 7, 2018 at 9:43

answered Jan 6, 2018 at 12:56

Madhuri

771 gold badge1 silver badge7 bronze badges

1 Comment

Peter Over a year ago

#1054 - Unknown column 'holdings.userId' in 'where clause'

Collectives™ on Stack Overflow

Optimize 3 MYSQL Queries to one

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related