This solves a problem i was thinking about for the last couple of days. Thank you! I was not even thinking of this kind of solution, clicked on this video because i didn't know what triggers were.
Bad triggers can sometimes fail the main SQL statement, that's the main reason to minimize using them. The example in the video is nice and simple, so no problem there. We like to use triggers for audits, to save changes to an audit table with an extra timestamp. Some databases even support enable/disable triggers. Very convenient for triggers that are not needed all the time.
Just create a stored procedure performing the actual action and use the trigger to trigger the execution of the stored procedure. As they are typically compiled, this should also minimize the execution time. Imho there should never be complex SQL inside of a trigger.
Server concepts are both deep and wide, I appreciate your work to expose developers to the concepts. Triggers can be very helpful but like everything in a SQL database, you can create some real problems with them as well. I highly suggest if people are going to get into SQL databases that they deep dive into triggers before using them. They are very much a double-edged sword with places where they are almost required, and other places where they simply shouldn't be used.
I'm not very keen on databases but storing the number of likes as a number in the `post` table goes against the "never store what you can compute" principle, but I guess in this case it's reasonable since, like you said, counting the number of likes every time a post is shown may take too long and it may even be a wasted computation if a post hasn't received any more likes between two retrievals of the same post. I'd be interested to know other cases where this kind of redundancy improves performance or is deemed better. Great video as always! :)
Thank you, I was thinking of the exact same advantages and disadvantages, most notably that you'd have to implement it multiple times for multiple apps; or, if you put it in a library, you'd still have to make sure all the developers knew to use it and didn't forget. Also, one app could be in Python and another in Ruby, e.g. But I'm thinking another advantage is that, if you have several different databases for different apps on the same MySQL server, you can put all that code in one place. Maybe a potential disadvantage would be that the devs no longer see that it's happening in their code, but in the case of change logs, for example, they don't even need to see it happening; it's just there for posterity... does that make sense?
I'm stuck brother, I need to use trigger to insert the max values from trigger table to insert it in another table group by ID, can you help me with that? I appreciate your help
No, MSSQL has triggers as well and they work the same way. Stored Procedures (sp) are used much like functions in other languages. Example, you might have a function named getBestPrice which when passed an SKU would check tables to find the best price. This could call other sps as well. Another good use of sps is reporting. You can have a sp such as getSalesByCustomer, getSalesByItem, getSalesByCategory.....
Counting the likes in the likes table up to 150000 times for each post id can certainly be inefficient but doing an update 150000 for each like in the posts table would be equally inefficient right?
The difference would be that the update is incurred only once per like rather than per load and the update query is very fast since it's updating on the primary key.
@@EngineerMan Sorry, I didn't mean to say that the posts table has to be updated 150k times for each like but that since the update query would be run once per like, eventually it would be no more efficient than counting the rows in the likes table 150k times. But, as you mentioned, if running the update query on the primary key is faster, then yeah..it does make sense to use a trigger. Could you consider doing a similar video on stored procedures please? Just like with triggers, there seems to be no consensus on if/when to use it. Thanks.
@@Chris-ov8pi After rereading my comment I think I caused some confusion. All I meant was that the performance penalty for inserting 150k records would be 150k update calls. Even if 150k update queries took, say, 30 seconds combined (it would take much less), and it takes say 3 seconds to count 150k records, only 10 people would be able to view that post (and all the likes) before it becomes inefficient. When and when not to use triggers is a tricky business. Triggers can make a mess when used wrong.
I think the difference is that not everyone who views the post will like it. The trigger to increment the like count only runs when someone likes it; not every time the post loads. Reading that value (or counting the likes on the fly) runs every time the post loads. It's not possible for there to be more likes than views, but it is almost inevitable that there will be more views than likes. So, using triggers, you read one value, increment it, and update it exactly once every time someone *likes* the post. Using on-the-fly counting, you read 150,000 records and write 1 record every time someone *views* the post.
Sadly there is no good MySQL plugin. I'm constantly on the lookout for such a thing. The only complete looking database plugin in know of is mssql but, as the name implies, that is for MS SQL Server. Because of this I have to use an external application. Previously I used SQL Workbench but because i used multiple versions of MySQL i switched to dBeaver. Although it has some bugs it downloads the appropriate driver for each connection and it helps a lot when editing/pasting multiple rows.
Where your channel was 10 years ago when I needed to learn all of this? Very helpful, well done.
This solves a problem i was thinking about for the last couple of days. Thank you!
I was not even thinking of this kind of solution, clicked on this video because i didn't know what triggers were.
Literally finished writing a rating system that could really benefit from triggers. Perfect timing and such a great reference for me to look at.
Let's see how many times insert_likes_youtube_trigger gonna trigger :D
Thank you Engineer Man! ::Add Epic Echo Voice:: Flaps cap in CHASSIS fan exhaust.
Bad triggers can sometimes fail the main SQL statement, that's the main reason to minimize using them.
The example in the video is nice and simple, so no problem there.
We like to use triggers for audits, to save changes to an audit table with an extra timestamp.
Some databases even support enable/disable triggers. Very convenient for triggers that are not needed all the time.
Just create a stored procedure performing the actual action and use the trigger to trigger the execution of the stored procedure. As they are typically compiled, this should also minimize the execution time. Imho there should never be complex SQL inside of a trigger.
Server concepts are both deep and wide, I appreciate your work to expose developers to the concepts. Triggers can be very helpful but like everything in a SQL database, you can create some real problems with them as well. I highly suggest if people are going to get into SQL databases that they deep dive into triggers before using them. They are very much a double-edged sword with places where they are almost required, and other places where they simply shouldn't be used.
True, but there is practically no alternative, when it comes to replicating data to remote, but different databases in real time.
4:35 solved my problem, thanks engineer man.
I'm not very keen on databases but storing the number of likes as a number in the `post` table goes against the "never store what you can compute" principle, but I guess in this case it's reasonable since, like you said, counting the number of likes every time a post is shown may take too long and it may even be a wasted computation if a post hasn't received any more likes between two retrievals of the same post. I'd be interested to know other cases where this kind of redundancy improves performance or is deemed better. Great video as always! :)
Generally I agree with you. Of course, I choose the specific use case that is a good candidate for triggers and thus a constantly calculated field.
Simple and straight to the point! Great video
Thank you, I was thinking of the exact same advantages and disadvantages, most notably that you'd have to implement it multiple times for multiple apps; or, if you put it in a library, you'd still have to make sure all the developers knew to use it and didn't forget. Also, one app could be in Python and another in Ruby, e.g.
But I'm thinking another advantage is that, if you have several different databases for different apps on the same MySQL server, you can put all that code in one place. Maybe a potential disadvantage would be that the devs no longer see that it's happening in their code, but in the case of change logs, for example, they don't even need to see it happening; it's just there for posterity... does that make sense?
Very good video. I will be sharing with some Jr devs I know for sure.
Thanks pal, very useful tutorial, go ahead.
Very well explained. Thank you.
Thanks... Good info!
Hi engineer man, What software do you use to do this video recording?
he uses OBS
Thanks! 🙂
I'm stuck brother, I need to use trigger to insert the max values from trigger table to insert it in another table group by ID, can you help me with that?
I appreciate your help
very useful, thanks!
5:23, why not use \g as in "show triggers \g"
Awesome, thanks man!
Trigger always associate with table or not?
Thanx💎💎💎💎💎
Thank you
So is this like a stored procedure in other DB software like MSSQL?
No, MSSQL has triggers as well and they work the same way. Stored Procedures (sp) are used much like functions in other languages. Example, you might have a function named getBestPrice which when passed an SKU would check tables to find the best price. This could call other sps as well. Another good use of sps is reporting. You can have a sp such as getSalesByCustomer, getSalesByItem, getSalesByCategory.....
Thx u that was so good info . 👍
You should use triggers, but you must understand where else they are.
What is regression
Counting the likes in the likes table up to 150000 times for each post id can certainly be inefficient but doing an update 150000 for each like in the posts table would be equally inefficient right?
The difference would be that the update is incurred only once per like rather than per load and the update query is very fast since it's updating on the primary key.
@@EngineerMan Sorry, I didn't mean to say that the posts table has to be updated 150k times for each like but that since the update query would be run once per like, eventually it would be no more efficient than counting the rows in the likes table 150k times.
But, as you mentioned, if running the update query on the primary key is faster, then yeah..it does make
sense to use a trigger.
Could you consider doing a similar video on stored procedures please? Just like with triggers, there seems to be no consensus on if/when to use it. Thanks.
@@Chris-ov8pi After rereading my comment I think I caused some confusion. All I meant was that the performance penalty for inserting 150k records would be 150k update calls. Even if 150k update queries took, say, 30 seconds combined (it would take much less), and it takes say 3 seconds to count 150k records, only 10 people would be able to view that post (and all the likes) before it becomes inefficient. When and when not to use triggers is a tricky business. Triggers can make a mess when used wrong.
I think the difference is that not everyone who views the post will like it.
The trigger to increment the like count only runs when someone likes it; not every time the post loads. Reading that value (or counting the likes on the fly) runs every time the post loads. It's not possible for there to be more likes than views, but it is almost inevitable that there will be more views than likes.
So, using triggers, you read one value, increment it, and update it exactly once every time someone *likes* the post. Using on-the-fly counting, you read 150,000 records and write 1 record every time someone *views* the post.
Sir... You are a genius and my savior. This is what I need!
Is it the same with MariaDB?
Yep, all should work well in MariaDB.
Hi people, I'm a brand new IT student. Which extensions do you recommend for MySQL/Oracle/etc.. in Visual Studio Code ? Thanks in advance
Sadly there is no good MySQL plugin. I'm constantly on the lookout for such a thing. The only complete looking database plugin in know of is mssql but, as the name implies, that is for MS SQL Server. Because of this I have to use an external application. Previously I used SQL Workbench but because i used multiple versions of MySQL i switched to dBeaver. Although it has some bugs it downloads the appropriate driver for each connection and it helps a lot when editing/pasting multiple rows.
I want course index , procedure mysql
Whats wrong with his eyebrow ?
First btw