Below is my query which take 10 seconds almost to excute data i am new in this i dont know how to optimize this query
SELECT
dataa.,
fn_ranks('user_ranks',dataa.pool_id, dataa.user_id, dataa.pp_id) AS userranks,
( if( fn_ranks('user_ranks', dataa.pool_id, dataa.user_id, dataa.pp_id)
BETWEEN fn_firstrank( 'firstrank', dataa.pool_id, dataa.user_id, dataa.pp_id)
AND fn_lastrank( 'lastrank', dataa.pool_id, dataa.user_id, dataa.pp_id), 'win', 'lose')) AS poolstatus,
if(( usedamount fn_allrank( 'getalluserrank', dataa.pool_id, dataa.user_id, dataa.pp_id,
'', '')) > usedamount, 'positive', 'negative') AS cuurentinvestkeyvalue
FROM
( SELECT
val.,
( fn_allrank( 'getalluserrank', val.pool_id, val.user_id, val.pp_id, '', '' )
(p_virtual_currency)) AS currentinvestamount,
( ( ( fn_allrank( 'getalluserrank', val.pool_id,val.user_id, val.pp_id, '', '' )
( p_virtual_currency)) - usedamount)
/ ( usedamount )) 100 AS percentchange
FROM
( SELECT
myparam.,
SUM(myparam.investamount) AS usedamount
FROM
(SELECT
s.pool_id,
s.pp_id,
us.user_id,
p.p_pool_name,
inves_amount AS investamount,
p.p_virtual_currency,
( SELECT ( count(pm_torank) / (p.p_max_user)) 100
FROM
tbl_poolprizemoney AS pm
INNER JOIN tbl_pool AS p
ON pm.pm_poolid = p.p_id
WHERE
pm.pm_poolid = s.pool_id ) AS prizepercen,
MAX(pm_price) AS firstprice,
( SELECT
SUM( pm_price(pm_torank - pm_fromrank 1))
FROM
tbl_poolprizemoney
WHERE
tbl_poolprizemoney. pm_poolid = us.pool_id) AS totalprice,
p.p_max_user,
p.p_entry_fee,
pp.pp_start_time,
pp.pp_end_time
from
tbl_user_stock AS us
INNER JOIN tbl_stockpricemaster AS s
ON us.stock_id = s.stock_id
AND us.pool_id = s.pool_id
AND us.publish_id = s.pp_id
INNER JOIN tbl_pool AS p
ON us.pool_id = p.p_id
INNER JOIN tbl_poolpublish AS pp
ON us.publish_id = pp.pp_id
AND us.pool_id = pp.pp_poolidINNER
JOIN tbl_poolprizemoney AS pm
ON us.pool_id = pm.pm_poolid
WHERE
NOW() BETWEEN pp.pp_start_time AND pp.pp_end_time
AND p.p_status = 'Active'
AND us.user_id = 186
AND p.p_min_user <= ( SELECT
COUNT(fs_userid)
FROM
tbl_fivestock AS fss
WHERE
fss.fs_poolid = pp.pp_poolid
AND fss.fs_publishid = pp.pp_id )
AND us.user_id = ( if( (( SELECT
count(stock_id)
FROM
tbl_user_stock AS usu
WHERE
usu.user_id = us.user_id
AND usu.pool_id = s.pool_id
AND usu.publish_id = s.pp_id ) = 5), us.user_id, 0))
AND us.user_id = ( if( (( SELECT
sum(usus.inves_amount)
FROM
tbl_user_stock AS usus
WHERE
usus.user_id = us.user_id
AND usus.pool_id = s.pool_id
AND usus.publish_id = s.pp_id )
>= ( SELECT
p_min_spent_amnt
FROM
tbl_pool AS pl
WHERE
pl.p_id=s.pool_id )), us.user_id, 0))
GROUP BY
s.pool_id,
s.pp_id ) AS myparam ) AS val ) AS dataa
CodePudding user response：
From the APPEARANCE the hierachy of your gambling pool is something like
A PoolPublished is within a given time period X and Y
within Each PUBLISHING are multiple individual pools and may or not be active
within each pool id there is a prize money result
Within your PrizeMoney, you have what APPEARS to be a function call pm_price via
SUM( pm_price(pm_torank - pm_fromrank 1)) for the TotalPrice
but it also appears to be a column where determining the
MAX( pm_price )
Finally, all your calls to fun_allrank() vs fn_ranks() and dealing with what you had as "usedamount", that MAY need some adjustment. However, with the complete LACK of table structures, and feedback of what you were trying to do, I think that MOST of what I have here WILL simplify the process and improve performance for you.
You will notice that I am pre-aggregating from the published pool activity FIRST to get: published date/time info, pool name, limits, fees, min/max pool sizes, etc., but ONLY for those within the current time period going on. No sense querying what could be years of data. Only from that does it also query for prize money and per-user stock/investment information. Since the groupings are all grouped by respective publish id, pool id and ultimately per user within a given pool, the front-most numbers SHOULD be very close to what you are expecting (before all the rank callings). If something is amiss, you will need to provide more info by editing you EXISTING post with data as needed for clarification.
select
AP.publishid,
AP.poolid,
AP.PoolName,
AP.MinimumPoolSize,
AP.MaximumPoolSize,
AP.PoolCount,
AP.PoolCurrency,
AP.PoolEntryFee,
AP.PoolMinimumSpendAmount,
AP.PublishPoolStartTime,
AP.PublishPoolEndTime,
Prize.FirstPrice,
Prize.PriceCount,
Prize.PriceCount / AP.MaximumPoolSize * 100.0 as PricePercent,
Prize.SumTotalPrice,
UserStock.User_ID,
UserStock.StockCount,
UserStock.InvestAmount,
-- Now get ranks of a given user against all
fn_allrank( 'getalluserrank', AP.poolid, UserStock.user_id, AP.Publishid, '', '' ) AP.PoolCurrency CurrentInvestAmount,
( fn_allrank( 'getalluserrank', AP.poolid, UserStock.user_id, AP.Publishid, '', '' )
AP.PoolCurrency - UserStock.InvestAmount) / UserStock.InvestAmount * 100.0 PercentChange,
-- and finally to individual user ranks?
fn_ranks( 'user_ranks', AP.poolid, UserStock.user_id, AP.Publishid ) UserRanks,
if( fn_ranks('user_ranks', AP.poolid, UserStock.user_id, AP.Publishid )
BETWEEN fn_firstrank( 'firstrank', AP.poolid, UserStock.user_id, AP.Publishid)
AND fn_lastrank( 'lastrank', AP.poolid, UserStock.user_id, AP.Publishid)
, 'win', 'lose') PoolStatus,
if( UserStock.InvestAmount
fn_allrank( 'getalluserrank', AP.poolid, UserStock.user_id, AP.Publishid, '', '')
> UserStock.InvestAmount, 'positive', 'negative') CuurentInvestKeyValue
from
-- start by getting all ACTIVE Published pools within the time period in question
-- get (via max() other parts from publish and pool while we are here once up-front).
( select
pp.pp_id publishid,
pp.pp_poolid poolid,
max( p.p_pool_name ) PoolName,
max( p.p_min_user ) MinimumPoolSize,
max( p.p_max_user ) MaximumPoolSize,
count(*) PoolCount,
max( p.p_virtual_currency ) PoolCurrency,
max( p.p_entry_fee ) PoolEntryFee,
max( p.p_min_spent_amnt ) PoolMinimumSpendAmount,
max( pp.pp_start_time ) PublishPoolStartTime,
max( pp.pp_end_time ) PublishPoolEndTime
from
tbl_poolpublish AS pp
JOIN tbl_pool AS p
ON pp.pp_poolid = p.p_id
AND p.p_status = 'Active'
JOIN tbl_fivestock fs
ON pp.pp_id = fs.fs_publishid
where
NOW() BETWEEN pp.pp_start_time AND pp.pp_end_time
group by
pp.pp_id,
pp.pp_poolid ) AP
-- pre-aggregate prize money info
-- using same WHERE condition to only get those published pools within same time period
JOIN
( select
pp.pp_id publishid,
pp.pp_poolid poolid,
count(*) as PriceCount,
-- see comment about PM_PRICE as column vs parameterized function in two below?
max( pm.pm_price ) FirstPrice,
sum( pm_price( pm.pm_totrank - pm.pm_fromrank 1 )) SumTotalPrice
from
tbl_poolpublish AS pp
JOIN tbl_pool AS p
ON pp.pp_poolid = p.p_id
AND p.p_status = 'Active'
JOIN tbl_poolprizemoney pm
ON pp.pp_poolid = pm.pm_poolid
where
NOW() BETWEEN pp.pp_start_time AND pp.pp_end_time
group by
pp.pp_id,
pp.pp_poolid ) Prize
-- join on same prequeried active pools by publish / poolid
on AP.Publishid = Prize.publishid
AND AP.PoolID = Prize.poolid
-- NOW, only from those pools published and active within the given time period,
-- we can join to others such as users participating in a published pool
JOIN
( select
pp.pp_id publishid,
pp.pp_poolid poolid,
usu.user_id,
count(*) as StockCount,
sum(usu.inves_amount) InvestAmount
from
tbl_poolpublish AS pp
JOIN tbl_pool AS p
ON pp.pp_poolid = p.p_id
AND p.p_status = 'Active'
JOIN tbl_user_stock AS usu
ON pp.pp_id = usu.publish_id
AND pp.pp_poolid = usu.pool_id
-- since all other tables now joined by respective publish and pool ID,
-- dont see a need for the stock price master as per publish/pool and user
-- are already resolved an no other columns appear to be used from the table
where
-- and you only cared about a SINGLE USER
usu.user_id = 186
AND NOW() BETWEEN pp.pp_start_time AND pp.pp_end_time
group by
pp.pp_id,
pp.pp_poolid,
usu.user_id ) UserStock
-- join on same prequeried active pools by publish / poolid
on AP.Publishid = Prize.publishid
AND AP.PoolID = Prize.poolid
where
AP.PoolCount >= AP.MinimumPoolSize
-- AND the user met the minimum spend investment amount and count
AND UserStock.StockCount = 5
AND UserStock.InvestAmount >= AP.PoolMinimumSpendAmount
CodePudding user response：
Typo in this line?
/ ( usedamount )) 100 AS percentchange
Suggested indexes:
fss: INDEX(fs_poolid, fs_publishid)
p: INDEX(p_id, p_max_user)
p: INDEX(p_status, p_min_user, p_id)
pl: INDEX(p_id)
pm: INDEX(pm_poolid)
pp: INDEX(pp_start_time, pp_end_time, pp_id, pp_poolidINNER,
s: INDEX(pool_id, pp_id)
s: INDEX(stock_id, pool_id, pp_id)
us: INDEX(pool_id)
pp: INDEX(pp_poolid, pp_id)
usu: INDEX(user_id, pool_id, publish_id)
us: INDEX(user_id, stock_id, pool_id, publish_id)
pp_poolid) usus: INDEX(user_id, pool_id, publish_id, inves_amount)
When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.
If any of these start with the PRIMARY KEY, then don't add an INDEX.
For DRapp's version, these might help:
fs: INDEX(fs_publishid)
p: INDEX(p_id, p_status)
pm: INDEX(pm_poolid, pm_price, pm_totrank, pm_fromrank)
pp: INDEX(pp_start_time, pp_end_time, pp_poolid, pp_id)
UserStock: INDEX(StockCount, InvestAmount, User_ID, user_id)
usu: INDEX(user_id, publish_id, inves_amount, pool_id)
Re COUNT:
COUNT(*)counts the number of rows -- fastest, easiest to optimizeCOUNT(fs_userid)counts the number of rows withfs_userid IS NOT NULL, so it may be slower thanCOUNT(*)COUNT(DISTINCT fs_userid)is likely to give a smaller number.
Which did you want?