I have never used, and probably will not, but I have to admit, that's incredibly powerful. Super educational information as usual. I think the only thing missing in some videos would be some real-case measurements and testing. For example, there could be two tables: one with JSON elements and another without. We could compare the speed of SELECTs from a million table data. I think this will not only be useful but also very informative about what kind of tables or schemas are more suitable for specific case scenarios. For example It could help us make informed decisions when creating product items attributes in a shop, whether it's better to use JSON or just columns. MYSQL is really powerful ;)
Three minutes into watching this, and I'm already loving the wonderful variety of use cases you have come up with! Feels like a NoSQL approach within a relational db
I don`t really often leave comments but you deserved it)) Ngl, I`ve never seen neither more useful, nor "pleasant-to-listen" tutorials in MySQL topic. The way you, Aaron, explain things to us is out of this world, do love it! Keep it up, I`m completely sure many people will find it useful!
those videos you make are really great. I worked with mySQL since 15 years ago but I still learn something new and really enjoy the format and your delivery of these topics!
Excellent video and resource link to Digital Ocean. I was living in dark ages using MySQL version 5.2x. I upgraded to 8.0.35 and experimented with everything you mentioned in the video. It opened my eyes on how to use MySQL with JSON moving forward. I was using a text field for that before and did not know about all the JSON related functions of MySQL. Thank you very much.
10 месяцев назад
I did exactly that in a postgresql db. This is fantastic, much more solid and fast
I just stumbled across this video and I haven't finished it but I already love it! I'm still learning about databases but I thought NoSQL was hands down more powerful and scalable than SQL tables, and my previous experience with SQL tables has been a drag, but this is showing me a side of SQL tables I didn't know existed. Thanks!
I'd like to add to this. Some of the reasons for using NoSql are being able to store the data in RAM like using Redis for cache etc and also NoSql is scalable. Both of those also possible in Sql databases. You can definitely configure your db to be stored in the memory and you can definitely have a cluster of Sql database. It is harder to scale up relationships with both Sql and NoSql. NoSql just defaults to having no relationships what so ever. That's what makes it easier to scale. If you do introduce relationships in your data, you'll have the same issues. Needless to say Sql databases are perfectly capable of not using relationships
We often have an "options_json" column where we set configurations of things that we don't directly search often. An example would be on our forms builder, the forms settings someone picks we use to generate the form, or even on questions if a certain question type is required or has visibility requirements, etc. It saves scanning a ton of extra columns.
Right, I had kinda assumed that, while you could, you wouldn't really want to search using JSON data columns. I've just been using it for mostly what he mentioned, metadata, settings, and I like the idea of tenant-specific data structures as I'm currently working in a multi tenant app that we're building out integrations for.
@@PlanetScale Some things on top of my head: 1. For scalability (concurrent connections) and performance (read/write speeds over single instance and cluster) 2. Why PlanetScale is MySQL instead of Postgres 3. Differences between them, what can Postgres do that MySQL can't and vice versa 4. When to use what Would be great if these are covered!
I tried working with json data in mysql, besides the syntax being clunky (compared to postgres) I found it to be super slow compared to structured data, I was processing millions of rows and anything related to json added a significant load on the database sometimes to the point of rendering it useless
Interesting! I am just learning dev and my first foray into it is with WordPress. I know WordPress plugins like Elementor use JSON for the pages you build in them and I wonder if they use this feature of MySQL.
Unreal the quality of content you are putting out. I've made the decision to not use NoSQL db's for certain services thanks in part to this video. Keep it up. Super informative.
Another idea for JSON data: audit history. For example, don't just have a created_at and updated_at for a user, but also history which contains a log of when the update happened, what changed and who initiated the change.
I am trying to understand 2 points 1. Performance: Which 1 is expensive, Performing JSON operations on mysql or in my backend code 2. Cost: Lets assume we need RDBMS but to store json data if we are using MongoDB then, Is MongoDb will be costly over Mysql if we plan to keep json data into MongoDb instead of Mysql? Please share your expert advice. Thanks for wonderful videos. 😃
I wish we had such a great video about JSON on PostgreSQL as well. But clearly I'm saying that on a video by the wrong people to ask about this. PS 30:18 funny how select * took 3ms, but select *, attributes took 1ms. I assume it was just caching, but looked wrong compared to what you said.
You are partially correct, json documents are fully supported in many SQL databases including MySQL But to imply that this was the only advantage of NoSql is wrong Storing dynamic data like json wasn't the advantage of NoSql, the massive advantage of using NoSql is horizontal scalability made a lot easier than any SQL database, that's why you should use NoSql if you need to ever work on apps with large data, don't use MySQL in that case.
So how about how to migrate from NoSQL mongodb to Mysql MariaDB
10 месяцев назад
Your title is misleading : NoSql databases is not limited to Documents/Json! Graphs, key/value, column, timeseries also exists and have their own pro and cons like relational database...
You’re assuming the reason for using a NoSQL database is wanting to store and index json data. I find often it comes down to scaling writes and data volume.
It sounds great in theory but in practice It's not the best idea :/. It may work if you don't need to query data inside of the JSON or for some shallowly nested data but if you're not careful then the performance overhead can get out of hand quickly not mentioning serious maintainability problems.
SQL is useful when the data model and use case fits. SQL is terrible for network config db's and automation at telco level. At least for that stuff, we only use graph.
Json in a MySQL field makes so much sense that I have a hard time imagining people that don't see this as an advantage. Unless you need to store BIG data managing a Json in a MySQL solves so many problems in development and is very flexible if in the future you want to move your Json to a noSQL instead of adding undress of tables and filed and get mad with conversation.
You absolutely need NoSQL - no relational DBMS can match a performance of a dedicated, say, graph DB. Imagine a use case - a database for storing CAD data. It's mostly a DAG (but not always strictly a DAG, cycles are possible). It's very strictly typed - what nodes can be linked is defined in schema. You need ACID, of course - multiple engineers work on the same large project at the same time and should not harm consistency. Good freaking luck doing it on top of a relational DBMS.
@@PlanetScale my point is, once performance is taken into account, there are tons of use cases where specialised non-relational DBMS is superior to any relational. Although the modern crop of non-relational systems is mostly disappointing, there is a lot of old powerful pre-relational systems.out there. So the blanket dismissal of non-relational and thoughtless defsulting to relational can be harmful.
The thing is, NoSQL was invented for horizontal scalability, which no RDBMS can allow due to obvious reasons. Handling dynamic JSON objects is merely a bonus.
Vitess has pretty good horizontal scalability! That's the whole reason it exists, in fact. We hit 1m QPS by just adding shards. planetscale.com/blog/one-million-queries-per-second-with-mysql.
@PlanetScale such throughput sounds nice. The question is , what kind of queries are we talking about. How would Vitess handle cross shard joins? And then, I can't help but think on the consistency part of CAP. How does it handle distributed transactions (cross shard)? In such setups, it is critical - does it do 2pc? Or does it do something based on a quorum?
JSON looks really powerful in mysql, I only want to do some research on what's the CPU expense when MySQL does these key & value selection compare to a NoSQL database, if that's cool for millions of records than I think it's great. I would love to discuss this, so please reply.
You kind of missed an important use case: event sourcing pattern. (although you mentioned storing api calls, fair enough). I’m not sure if MySQL is performant enough for diffing, or creating materialized views from json columns though.
I think this is the wrong direction. JSON should replace tables, not tables to add JSON columns. A tree(JSON) is more general than a list, its weird to add trees in a list. Things become complicated and queries verbose. I don't mean that MongoDB is ready to replace all relational databases, but they are in the right direction, they use a tree data model from the start.
I don't understand, postgresql had json type. MySQL has had it too? Wow. I am an idiot. What a effing idiot to have confidently said MySQL doesn't have json in an interview.
Great content, Aaron. I have been using the JSON type for quite a long time, but wasn't aware on the index part of that topic (and never was in need of it, yet). Aside from the typical log scenario (API Tx/Rx), I have been using JSON for event stream data, basically the same, but from an event sourcing view on the domain.
Great video, I am just starting to learn mySQL and postgres for work and this is really helpful as we have a JSON object in our database that I've been asked to generate a useful table for. Can I ask what software you are using in this video to write your queries and get the show the table results?
I've been using postgresql for storing jsonb documents for what feels like a decade. Absolutely no need for any other type of `nosql' database. Agree w/ the video here.
Sorry, but from my perspective examples show here are useless. When we store data (nested and more complicated structures) in db we want to paginate it, filter, etc. In video like this I would like to see more complicated examples and not the ones from documentation
Currently using a relational DB for a private project after a while of using exclusively MongoDB in the professional context, and this video has a perfect timing to solve one of the issues I had: having a list of (unknown amount of) tags for any row. Currently I am using extra link tables but a json column would do the job in a more elegant manner imo. Thank you!
Other than it can be annoying that Postgres insists that the case (upper vs lowercase) is correct.. but that can be a s good thing to stop you from being a sloppy coder
You have been doing really good effort for everyone, it's really nice. I was wondering if you need a video editor team of motionscope, to take your channel to new heights with professional editing.
This is interesting but isn't the purpose of NoSQL faster access to data? And is used to store loosely related data to access it faster why use OLTP for it?
There may be *some* cases where the data is purely unstructured, but I feel like most data has some structure and storing that part in MySQL makes a lot of sense
Never go SQL, awful 2005-like dx. Always go non-relational, preferably MongoDB as you can share schemas across your whole stack. If your stack isn't fully typescript based you're doing it wrong.
I think you'll find very few companies are fully typescript. Fully typescript is ok! But to suggest any other way is wrong is a bit narrow minded imo
10 месяцев назад
@@PlanetScale I was being a bit cheeky and over the top probably due to a mix of early morning and the intro being a bit of an attack; for that I apologize. A lot of great advice in the video; I just personally really dislike the SQL syntax, thats most likely due to me thinking of a database in terms of documents compared to say SQL which would be more of a spreadsheet. We're about to decide on DB solution for our primary big saas project and even though I'm the one making the tech choices as CTO and I again despise the SQL syntax I'm strongly considering going with postgresql (vercel storage etc) thanks to all of the tooling and the fact that the pool of hire-able developers is much larger. I'm really dreading it though.
After playing around with this today. Some useful notes for MariaDB users: 1.) The ->> operator is a shorthand for JSON_UNQUOTE(JSON_EXTRACT()) in MySQL 5.7.13 and later. However, MariaDB does not support this shorthand operator. You're stuck with JSON_UNQUOTE(JSON_EXTRACT(`jsonObject`, '$.your_key')). 2.) When attempting to create STORED or VIRTUAL columns; instead of STORED use PERSISTENT: eg: ALTER TABLE your_table ADD COLUMN your_column VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(`jsonObject`, '$.your_key'))) PERSISTENT; 3.) The INVISIBLE column feature is not supported in MariaDB. It's a feature introduced in MySQL 8.0.23, which allows you to make a column invisible. In MariaDB, you can't make a column invisible directly.
This info is super useful, at work we are evaluating ditching MySQL for PostgreSQL and get a NoSQL. I know nothing about NoSQL, is it possible to do a second part of this video explaining how these MySQL features compare to NoSQL?
Is there any technical reason at all to use MySQL instead of PostgreSQL? Does MySQL have any redeeming qualities that would make someone choose it over psql?
