我需要关于如何简化/改进MySQL中的订购模型的建议

我想知道是否可以简化以下过程:我想根据以下标准标记一个游戏,以便将其发送给客户:(如果您熟悉Netflix,它相当于他们的电影队列。)

1. 他们家里有多少场比赛。
2. 他们在计费月总共收到了多少个游戏。

我有一张表,它存储了客户的选择(他们的游戏队列),称为心愿单。我正在使用以下查询自动对新插入的记录的选择进行排名(排名决定发货的优先级):

schema (id, memberId, gameId, rank, markedForShipment, shippedOn, returnedOn)

        INSERT INTO wishlists (memberId, gameId, rank, markedForShipment)
        SELECT  @memberId, @gameId, COALESCE(MAX(rank), 0) + 1, 0, null, null
        FROM    wishlists
        WHERE   wishlists.memberId = @memberId

我希望根据两个条件自动用0或1填充“markedforshipment”: 1. How many games a member has already received this month 和 2. how many games a member currently has at home

目前,我在每次插入后都要执行以下操作:

1. 从计划表中获取计划限制(ShipmentsPerMonthLimit和GamesAthomeLimit)。

2. 找出有多少货( shipmentCount )一个成员这个月

       SELECT COUNT(shippedOn) AS shipmentCount
       FROM wishlists
       INNER JOIN members ON wishlists.memberId = members.id
       WHERE wishlists.memberId = @memberId
       AND wishlists.shippedOn >= members.billingCycleStart
       AND wishlists.shippedOn < DATE_ADD(members.billingCycleStart, INTERVAL 1 MONTH)
   

3. 找出有多少 gamesAtHome 成员有

       SELECT COUNT(shippedOn) AS gamesAtHome
       FROM wishlists
       WHERE memberId = @memberId AND shippedOn IS NOT NULL AND returnedOn IS NULL
   

4. 找出有多少排队的货物( markedForShippment 一名成员有

       SELECT COUNT(markedForShippment) AS queuedShipments
       FROM wishlists
       WHERE memberId = @memberId AND markedForShipping = 1
   

5. 查看游戏是否为库存。

如果以下全部为真,我将通过设置更新新插入的记录 markedForShipping to 1 .

gamesAtHome is < than gamesAtHomeLimit
shipmentCount is < than shipmentsPerMonthLimit
queuedShipments is < gamesAtHomeLimit

如何改进此过程?我可以简化一下吗?我提出这些问题时,要记住,绩效可能是最重要的因素。 4。