 All right. Hello everybody. This is going to be today's first and last talk in English you'll hear So I'm Otto I came all the way from Finland here to tell you about how to What you should know about the database. So I'm a long time Linux and open source advocate and I'm a I've contributed to WordPress core, but also to many other projects and I've contributed quite a lot to Maria DB So I know a few things about the database and I I work at a company called Seravo Which makes enterprise grade? Hosting and upkeep which means upkeep means that we guarantee our customers that the site works all the time No matter how much traffic they have or Even if their own coders sometimes make mistakes, we ensure that the site works all the time So why do I like to tell about the database because I think it's the the common common problems you have with WordPress is either that it gets slow or You might have security issues somebody hacks your web website and the database is related to both of them So I think it's important for developers to know something about the database. There's all of your Valuable data and it's often the bottleneck of performance. How fast your WordPress page this place So in my opinion the database is the single most important part of your WordPress infrastructure so I have ten tips for you and Tip number one is that learn how to make database dumps. How many of you know how to make a database dump? So that's very convenient because it's it you get a plain text version of your database And you can read exactly what it contains and you can even edit it and then import it again into your database And also it's important to know how to make database dumps in plain text because that's the only Interoperable way you can move your database from one system to another and from one database version to another You can do that using the traditional MySQL dump tool or using VP CLI So here is an example, this is the command I use to make database dumps and By the way There is my Twitter handle so my slides have quite a lot of code examples So you might want to look at them afterwards and I will post my slides in my Twitter account So you will find it there So here is an example how it looks like and you can for example Make two database dumps before and after you change a setting and then you can use diff for the text files to find out What changed in the database? That's fairly convenient and More more about the structure of the database here. You can see some some column Some table names and and values but more more you can find in the WordPress codex on that page Right. So tip number two learn how to use VP CLI It's very convenient for database operations You can quickly find out what are your biggest tables and you can search any string from your database and My favorite one is the last one The VP CLI command search and replace how many of you have used it Quite a few so this is an example how you can in your entire database change a HTTP address into the secure version very easily and quickly Then tip number three how many of you use PHP my admin Almost everybody. So my tip is the checkout Adminer. It's a smaller and faster and in my opinion better Graphical tool to browse your database Then tip number four, this is something that all WordPress developers should know so on every single WordPress page load this Query is made from the database Work does always gets the options on every single page load. So if you have a slow site, you can check Manually running this command SQL command in your database console to see how many rows it returns And how long it takes so if you have if the result is very big or slow, then that's going to slow down every single page load of WordPress and Here is a few is two examples if you notice that your VP options table is very big You can use these SQL commands to find out what are the What are the biggest fields and what rows are most common and WordPress doesn't have an index in this table at all because it's supposed to be By design less than one megabit all the time But if your database is bigger than that then you need to either clean it up or add an index Another table that often unfortunately grows big is the VP post meta table Especially if you have a VU commerce installed it will generate lots and lots of rows into this table and with this command You can find out what type of Rows are most common in your VP post meta table Unfortunately, lots of lots of plugins pollute this table and we need to raise some awareness among the plugin developers and team developers that you can't use The post meta functions too much because every single Time you save something it will generate one new row in the database So then tip number six learn SQL So their commands I show you how here are in the SQL language and that's something you can Command to the database and it will fetch the data for you So if you learn the basics about Selects and inserts and updates then you will be a better WordPress developer because you have some understanding of how the WordPress is talking to the database And if you're if you notice that you're doing something that you are using very very frequently or in high numbers The WordPress meta functions, then you should consider creating your own table That's not too hard, and it will be very good for performance And then when you learn SQL you should learn what an index and a full table scan means these are links to the documentation So if you find my presentation you can read more and Then tip number seven. I just told you to learn some SQL, but next I tell you don't use it directly You need to understand how it works, but you're not supposed to use it most of the time directly So WordPress has lots of built-in functions to do it. The most simple one is get posts If you're just getting posts use the built-in WordPress function It will do all the caching and optimizations for you If that's not good enough, you need to do something more complex use the VP query class And if that's not good enough, then you can use the VP DB get row and insert and other Methods and if that if not even that is good enough for you and you need to do your own SQL commands, then don't put them directly, but use the VP DB prepare and VP DB query methods It did it will protect you from SQL injections All right, so that was the part for For WordPress, but then you need to make sure that your database installation Installation is also good. So I would recommend Maria DB well obviously because I've been involved in its development But also many of you who like open source probably understand that something that's Managed by Oracle is not going to be a good open source product in the long run And then you need to use a recent version and then you need to make sure that all of your settings are okay That you have you're using the inner DB engine that your character set is correct And your collation is correct and so on and this is actually quite complex So if you are working on a very big project, then you maybe need to hire a database Expert to make sure that your data stuff is correct or or you can use a company and a Managed WordPress service that has taken care of all of these settings for you And then sometimes you shouldn't use the database at all And here is a quick example of how to use transients transients enables you to store Complex quarries the result of complex quarries in a single database row Or if you have ready or something similar installed it will be stored in memory and will be super fast How many of you have heard about the transient API in WordPress? Please check it out. It will help you make your site Make your own code much faster in many occasions alright, and then my last tip is That you should do something to monitor your performance. You can use the show process list in the console try it out or You can enable Maria DB as something called slow log So you can log for example all quarries that take more than five seconds and then analyze what's in the log and There's also tools like for example tidways that you can use to analyze what's happening in your php code and database in real-time in production And then my one final and extra tip is that never push your database into production This is something that unfortunately happens quite often because people when they develop WordPress sites They do some some things in code and some things in the settings and the settings are in the database And then they are tempted to push the database into production. That's that's bad. Try to do something else All right. Thank you. If you want to ask me something directly, we don't have time for more than one question right now Yes Wanted to ask one question regarding the collision of database because well in previous Developments I've run into old servers that are running like playing utf-8 instead of the md4 whatever was after and I would like to know if there is any important difference besides maybe some security concerns or something else because Sometimes I'm like forced to Change the collision database to work with these old servers But they shouldn't be sitting in a way that sometimes you run into them and there's no option around So I'd like to know if there's anything else besides well you use that emojis for example, but I know there's some security concern in there I think I Don't think there's any security concern But there are some usability concerns So when you are creating your database make sure that you choose the utf mb4 Character set because if you want to store emojis in your database in your comments For example, then you need to have this character set and then the collision means in what order is the results If you have a result in an alphabetical order, then in what order is the different characters? Put so that needs to be in your case the Spanish collation. All right, so the server for example does not allow these newer collation let's say because like I said they're very old servers They shouldn't be there in first place, but so then you can dump the database in the text format and Then change the collation and then import it back. Yeah, that's what that's what that's the safest and easiest thing to do All right. Thank you