# Yugabyte vs MySQL **URL:** \ **Category:** General\ **Created:** [October 2, 2019, 7:49am UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513 "2019-10-02T07:49:17Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 7:49am UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/1 "2019-10-02T07:49:17Z")
Hello! Now I use MySQL. In the next project I want to use another DB. Does your DB replace MySQL? Do you have a performance comparision? ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 2, 2019, 11:26am UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/2 "2019-10-02T11:26:17Z")
Hi @ivan_s Yes YugabyteDB is a great alternative to mysql. But performance metrics are different in a distributed db compared to a single node one. We have many benchmarks comparing to different dbs [Performance Benchmarks Library | Yugabyte](https://blog.yugabyte.com/category/performance-benchmarks/) including aws-aurora which has a similar architecture to mysql. There may be some feature that MySQL has that PG/YugabyteDB doesn’t but I’m sure it can be expressed in another way. You can explain your app more or the features that you need and I can guide you in the right direction! ---
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 12:34pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/3 "2019-10-02T12:34:08Z")
This is the messenger. The bottleneck is message storage (100 million per day). As far as I understand, Nosql (like Cassandra) is well suited for this. We also need to query the columns. For example, there are users (first table), and they have contacts (second table). It is necessary to make a request on the user\_id column in the table with contacts. As far as I understand, only RDBMS can solve this. Is your database suitable for this? Now we are using Mysql. But it is slow and cannot be scaled easily. How long will a new column be added to the table? Let’s say 300 million rows in a table. Do I need to split your database into two independent databases on different clusters or can all this be combined into one solution? ---
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 12:37pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/4 "2019-10-02T12:37:37Z")
> > But performance metrics are different in a distributed db compared to a single node one. - For better or worse? ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 2, 2019, 1:17pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/5 "2019-10-02T13:17:46Z")
Your usecase is perfect for YugabyteDB. With the right partitioning you can have efficient writes and queries with linear scaling. Regarding contacts table, you mean `select * from contatcts where user_id=x` ? If not explain it in a sql query ? Adding a new column is instant cause it doesn’t need to rewrite the whole table. I’m guessing a column that can be null. - re performance It will be better since you can use multiple nodes with more storage/memory/cpu. It will worse for global transactions that span multiple servers. It will be better for transactions that are inside 1 partition/server. - re Do I need to split your database into two independent databases on different clusters or can all this be combined into one solution? This can all be in 1 cluster. Doing a cluster-per-feature is mostly needed when access-patterns,requirements,sla,hot/cold data change a lot between features . I think you can use 1 cluster at first and slowly migrate only when necessary. You can explain more your app, write-path and read-path so I can help on how best to use a schema that horizontally scales. ---
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 3:24pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/6 "2019-10-02T15:24:48Z")
Thanks! > > It will worse for global transactions that span multiple servers. - Can you give an example? > > You can explain more your app, write-path and read-path so I can help on how best to use a schema that horizontally scales. - I don’t quite understand what you want. What’s “write-path&read-path”? Should I use YSQL or YCQL? How to make a choice? I have a large number of messages, which seems to be more suitable for YCQL. At the same time, I need to use the RDBMS for other tasks, which seems to be more suitable for YSQL. ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 2, 2019, 3:48pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/7 "2019-10-02T15:48:12Z")
> [@ivan\_s](#): > > Can you give an example? Distributed transactions on every database that exists are slower compared to single node. Suppose you have 2 channels. 1 channel is on node1, other channel on node2. If you want to update a row on both channels, so on both nodes, in 1 transaction, it will be slower (because of network coordination) to do that compared to separated transactions for each node. If your transaction is only targeting channel1, it will be as fast as mysql/postgresql (faster/slower depending on the query since the internals change a little). > [@ivan\_s](#): > > Should I use YSQL or YCQL? How to make a choice? I have a large number of messages, which seems to be more suitable for YCQL. At the same time, I need to use the RDBMS for other tasks, which seems to be more suitable for YSQL. You can use both. Or depends on what you need. Currently YCQL is a little more optimized and the client driver is cluster-aware. While YSQL drivers need a little work to be more efficient on cluster mode (can be done manually by a custom connection pooler or using [GitHub - yugabyte/jdbc-yugabytedb: JDBC Driver for Yugabyte SQL (YSQL)](https://github.com/yugabyte/jdbc-yugabytedb) in java). But YSQL is getting optimized to be as efficient as YCQL. And in complex queries, it will be faster since it will pushdown data locally. The whole thing is: partition your data as optimal as possible, and try to do most queries in single partitions. So you get best of both worlds. And I can help you with that. ---
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 4:03pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/8 "2019-10-02T16:03:23Z")
Thank you for the answer. “Node” is a server, I understand that. What do you mean by “channel”? ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 2, 2019, 4:08pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/9 "2019-10-02T16:08:07Z")
> [@ivan\_s](#): > > What do you mean by “channel”? An example if you decide to keep all messages of a channel in 1 server, so partitioning by `message.channel_id`. ---
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 4:35pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/10 "2019-10-02T16:35:42Z")
Thank you! Correctly I understand that if we separate transactions for each node, then when reading we can get an old record or nonexistent? Because we received from a node on which the value has not yet changed? For example, before saving a message, we check if we have such a message or not (messages come from other messengers). Sometimes two scripts execute this at the same time and the first one can already save the message, and the second one does the check, does not receive the value and also saves the message. ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 2, 2019, 4:50pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/11 "2019-10-02T16:50:50Z")
> [@ivan\_s](#): > > Correctly I understand that if we separate transactions for each node, then when reading we can get an old record or nonexistent? Because we received from a node on which the value has not yet changed? write/delete/update are synchronously replicated > [@ivan\_s](#): > > For example, before saving a message, we check if we have such a message or not (messages come from other messengers). Sometimes two scripts execute this at the same time and the first one can already save the message, and the second one does the check, does not receive the value and also saves the message. Do both messages share the same primary key ? When you say `check`, what’s the query ? ---
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 5:17pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/12 "2019-10-02T17:17:58Z")
> > write/delete/update are synchronously replicated If did we separate transactions for each node? Or why are we separating transactions? > > When you say `check` , what’s the query ? We get a message. The message has unique values ​​item\_id and client\_id. Next, we need to check whether such a client exists in the table with clients: SELECT \* FROM clients WHERE service\_id = $clientId; If it does not exist, then we create entries: 1. in the table with clients; 2. in the table with dialogs (we create a dialog to which messages are attached). 3. We save the message. If the client exists, we check if the message exists: SELECT \* FROM messages WHERE service\_id = $itemId; If the message does not exist, we save it in the messages table. What problems: 1. The dialog does not have a unique item\_id, and it can be saved twice. To save once, you need to check if the client exists. 2. Messages have a unique item\_id, but for message to be exactly unique, we need a composite index for the following columns: item\_id, client\_id, account\_id (we did in mysql this way). ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 2, 2019, 5:47pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/13 "2019-10-02T17:47:20Z")
> [@ivan\_s](#): > > If did we separate transactions for each node? Or why are we separating transactions? Can you read these and reply back if it’s not clear ? > **[Design goals](https://docs.yugabyte.com/preview/architecture/design-goals/)** > > Learn the design goals that drive the building of YugabyteDB. > **[DocDB storage layer](https://docs.yugabyte.com/preview/architecture/docdb/)** > > Learn about the persistent storage layer of DocDB. > **[Core functions](https://docs.yugabyte.com/preview/architecture/core-functions/)** > > Learn about the internals of YugabyteDB in the context of the core database functions. > [@ivan\_s](#): > > in the table with clients; This is easy: (assuming you do similar thing) 1. select 2. if it doesn’t exist, insert 3. if unique-index-error, ignore Or can be done in 1 command with an upsert. > [@ivan\_s](#): > > The dialog does not have a unique item\_id, and it can be saved twice. To save once, you need to check if the client exists. > [@ivan\_s](#): > > The dialog does not have a unique item\_id, and it can be saved twice. To save once, you need to check if the client exists. There is 1 dialog per client ? I’m not understanding the columns of the dialog. > [@ivan\_s](#): > > Messages have a unique item\_id, but for message to be exactly unique, we need a composite index for the following columns: item\_id, client\_id, account\_id (we did in mysql this way). Explain schema with queries. And simple if/else where things may break. And put in code blocks. ---
**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\ **Post date:** [October 2, 2019, 7:13pm UTC](https://forum.yugabyte.com/t/yugabyte-vs-mysql/513/14 "2019-10-02T19:13:17Z")
Thanks. I will study the documents and answer later. You are very loyal.