Mysql To MongoDb design Issue

Question

I am planning to move my mysql db of chat_latest and chat_messages to mongodb.

Old Mysql tables:

chat_latest:

User1 User2 LatestState
1      2     Accepted
1      3     Declined

chat_messages:

sender datetime body   stateWhileMessageWasSend   datetime
1      2     hiii 0     SenderInitiated
2      1     hiii 1    recAccepted
2      1     hiii 2     recAccepted
1      3     hiii 0     SenderInitiated
3      1     hiii 1    recAccepted
3      1     hiii 2     recDeclined

which i moved to mongodb

db.chat.find().pretty().limit(3)
{
    "_id" : ObjectId("593921425ccc8150f35e7662"),
    "user1" : 1,
    "user2" : 2,
    "LatestState" : "Accepted",
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-08T10:04:50Z"),
            "body" : "hiii 0",
            "stateWhileMessageWasSend" : "SenderInitiated"
        },
        {
            "sender" : 2,
            "datetime" : ISODate("2017-06-09T10:04:50Z"),
            "body" : "hiii 1",
    "stateWhileMessageWhileSend" : "recAccepted"
        },
        {
            "sender" : 2,
            "datetime" : ISODate("2017-06-10T10:04:50Z"),
            "body" : "hiii 2",
    "stateWhileMessageWhileSend" : "recAccepted"

        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7663"),
    "user1" : 1,
    "user2" : 3,
"LatestState" : "Declined",
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-08T10:04:50Z"),
            "body" : "hiii 0",
    "stateWhileMessageWhileSend" : "SenderInitiated"
        },
        {
            "sender" : 3,
            "datetime" : ISODate("2017-06-09T10:04:50Z"),
            "body" : "hiii 1",
    "stateWhileMessageWhileSend" : "recAccepted"
        },
        {
            "sender" : 3,
            "datetime" : ISODate("2017-06-10T10:04:50Z"),
            "body" : "hiii 2",
    "stateWhileMessageWhileSend" : "recDeclined"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7664"),
    "user1" : 1,
    "user2" : 4,
"LatestState" : "Canceled",
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-08T10:04:50Z"),
            "body" : "hiii 0",
    "stateWhileMessageWhileSend" : "SenderInitiated"
        },
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-09T10:04:50Z"),
            "body" : "hiii 1",
    "stateWhileMessageWhileSend" : "senderCanceledLiking"
        }
    ]
}

Cases:

I need to find latest state between 2 user, which I can find easily.
I need to list all acceptance user where user1=1
need queries like Match conditions and latest date from array

Question:

Since my 30% queries are similar to case3, so is it a better design as I guess aggregation are slow.

Record Count: 300 million

You guess or you know? Because the aggregation referenced here simply does a query and filters content from the document. Which actually should have no more impact than simple projection. "Complex" aggregations are in fact slower than running a regular query. But that statement is neither complex nor actually aggregating anything. Since I actually wrote it, then I think I should know. — Neil Lunn, Jun 13 '17 at 07:35
@NeilLunn for 1lac record its taking approx 3 seconds { "keysExamined" : 320, "docsExamined" : 320, "millis" : 2620 } — , Jun 13 '17 at 08:35
What indexes do you have in place on the collection? This is not "aggregation" that is the problem because as I have already explained this is not "aggregating" anything. Poor performance is a matter of how you have your own data set up. If you want your question to be productive then give details of what indexing you have defined, if any. Please. — Neil Lunn, Jun 13 '17 at 08:37
And of course supply any details of query parameters actually being used. The only parameters you asked for to be supplied was examining the "sender" of the messages array. Therefore you should at least be indexing on that field, and perhaps we might actually be able to get some more information out of you such as *"is it possible to only select documents within a given date range"* or whatever actual limiting conditions you have in a real world use case. If you want real answers, don't ask such open ended questions and please be specific. — Neil Lunn, Jun 13 '17 at 08:40
I guess keysExamined = docsExamined= 320 means indexing are proper. Messages.sender is indexed — , Jun 13 '17 at 08:51
This question is really open ended the way you have written it. However if you can include the indexes you already have ( use `.getindexes()` on the collection ) and include the aggregation statement you are actually using ( which should be the first one, since you were told that ) as well as the output from [aggregation "explain"](https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/) then there is something to examine and advise on. Okay. Get to finding that information and add it too your question. — Neil Lunn, Jun 13 '17 at 09:04
@NeilLunn i will perform test on better servers now. Also can u think if this is a fine design ? — , Jun 13 '17 at 18:28
You have been asked to show details about current indexes and the process you are actually using. Please edit your question to include them. Without the specific detail your question "should" in fact be closed for being primarily opinion based. So I am telling you what you need to include to make it productive. — Neil Lunn, Jun 14 '17 at 01:41

Mysql To MongoDb design Issue

0 Answers0