 I kind of asked you already to talk about that in Helsinki New York. We are going to talk about it in a year, so we might have to talk about it in a test, you know? So, I'm going to send you some snack next week, so you can come next week at the initial. But anyways... Does this happen every time? This is the second work that I attend, and it's the second time I see what the BDA are. I think it's a battle. And also Daniel had exactly the same time as he had in Helsinki last year, so... There is something to be careful about, you know? And it's always the same people. So how many of you still in the room don't speak Finnish? Two. I know you do understand some Finnish, and you probably don't know because you're from Serbia, but you have to sign some English, so would you mind if I had a starting Finnish? Because it's late in the afternoon, so... So I'm going to tell you about ten things... Don't eat English! ...tell you English. So I'm going to tell you about ten things every developer should know about their database and WordPress to run optimally. I didn't have to do an extra spark, but... Well, I have a TV, and those who are US developers have seen this presentation. Right, so who am I? I also want to show an introduction to myself. So in that vision for working for WordPress, I have a very technical CEO, and I have a very passionate about open source, and I have used and advocated Linux in an open source software for 20 or so years, and I've also contributed to many of the projects I've contributed more to, this really big, so I think I'm suitable to talk about the database. And our company, Bebe Paulding Finnish, and my English is doing WordPress app, as you already know, this company was made all the way. I hear this all the time, and you probably know this very well. The two most common issues running into WordPress are the ability to security, speed, performance. And in both cases, the database actually makes the same problem. In performance, the database is quite often the bottleneck, in case of your data, and if your cycle is hacked and your data leaks, you don't have as many of them as you have in your database. As well, I would say that the database is the single most important piece of your WordPress infrastructure, and as a developer, if you want to be a professional developer, you need to understand at least the basics of how the database works. Right, so I have here 10 easy tips for you to take home and then do experiments. So first one is, learn how to make copies of your database, and how to make database dumps. If it dumps, so there's many benefits from it. First of all, it's plain text, if you make a database dump in plain text, you can easily browse the text file, and you can even modify it, and then import it somewhere else, and you get exactly the kind of database you want. And the database dump, as in this text format, is designed to be interoperable, so that you can take it from different database versions, export it, and import it to other places. You can do that with a binary form database. So learn how to do it. The classical command is minus to a dump, and if you have a VPCLI, you can also have a VPDB export. I hope you can see that, but here's my one-liner how-to-run VPDB export. So this guarantees that the state of your data is consistent, that if it takes a long time for the database dump to be generated, and there's new updates coming in with this command you're doing in a single transaction, so it's consistent that there's a lot of inconsistencies inside the database, and this also produces a text dump where every row in your database is a row in the file, so it's easy to read and edit. And what you can do with this, you can do it whatever you can do with text files. We can, for example, take a database dump, then do some changes in, let's say, some plug-in options, then make a new dump, and then do a gif between those dumps to see what actually was changed in the database by the plug-in. And here you can see some SQL language telling that in this example it's inserting into this empty parent's table, these values, and if you're interested to learn what the standard WordPress database consists of, this page will explain all the tables and the columns. Tip number two, learn VPDB CLI in any database command. There's actually many useful ones, so I already told you about import and export. You can list the size of your database tables to quickly figure out if your database is loaded or not. You can enter the minus to your or MariaDB console with VPDB CLI. You can do searches in the database, and the last one is my favorite, which I use all the time. If you can do a search through this in the database, for example, when you are moving a site from a development department to production or changing the address in production or something like that, you can just run this. In this example, I'm changing the other URLs to have HTTPS instead of HTTPS. Tip number three, use Alienware to browse the database. How many of you are using PHP, my library, or if you are, how many of you are using Alienware? Majority, cool. I think that's way better. So if you haven't heard about Alienware before, check it out. Tip number four, so did you know that in every single WordPress page load, this specific database point is always wrong? Do you know that? One year about it. So this is the table where WordPress stores options, and if the column autoload has the value yes, then this is something that PHP would read on every WordPress page load, and sometimes this table gets loaded, and with this command you can check how many rows it returns, how much data, and also when you're running any SQL manually from the console or through a tool editor, it will show you how long it took to create, to respond to this query. In this example, five minutes, which is good, or actually, yeah, five minutes too long, or it turns too much data in every single WordPress page load and your site load is low. And in the WordPress documentation, it said that this table is not supposed to be over one megabyte, and with this and the SQL tip, if your data, if your, if this query or if this table is bigger than one megabyte, with this query, you can find what are the biggest options. Sometimes, unfortunately, there are stupid plugins that pollute the options database where the plugin developer, and if your database, you should first try to clean up that your options database is not to be, and if you can't, it's completely impossible, then you should have an index to make that database load faster. So if your database is very small, then you shouldn't have an index because then the index will be overhead, but if your database is big, then you should have it compared to the production sites, this DB Postman, how many of you have run in this situation? So this is typical, for example, for WooCommerce or any other system or plugin that uses a lot of PostMeta data. So the problem with PostMeta is that if you have a post and the post has many fields like title and line and contents and so on, there are just one column in the database and all of your postings on one row in the database. The structure of the PostMeta table is different. You have a key and a value, it's basically a key value store, and then you have the contents of that store. So fields will create a new line in the database and across the and with this SQL command, you can you can check out what are the most common key names in your DB PostMeta database to find out what's below it. And by the way, there is my Twitter, so I'm going to post the slides on there and you can get copycats to just follow me and Twitter. Let me explain this in a little bit. So it's a select, so it fetches data and then it has this self-stream function. It takes the value in the mental key column and then it takes the characters 1 to 20 and then takes that key start and then it counts how many rows with that key start exists and then returns mist grouped by the key start and ordered by the count. So basically here's an example of what to get. So the point here is that in the DB PostMeta table there's lots of values that have the same beginning. So now I'm going to take it just in the 21st, 21st characters. The reason why I wanted to give this talk, you wait with you developers who care about the database or understand the basics for example. It always starts and quite often you haven't even noticed that there's 20s that have stupid codes that they keep filling in the database and eventually decide to go around to a full stop because if you fill in the database it will be those rows and there will be purged entity or there will be fun to raise awareness about the database. There's practices in this that are quite simple. Just look at the database, how big it is and what are the values in the database. Recommend that you are in SEO. You don't have to learn a lot but the basic commands like select, update, delete. So that if you read code and you see these commands then you have some clue what they might mean and you know how to Google for it. And also you should understand that database systems are very basic. All applications based on the database and the database systems we're having today are already quite mature. For example Maria, they've been developed in the 1990s, 1996. So it's now there's a huge amount of engineering and algorithms and performance built into the database code. So you should try to leverage with that and not just load everything from the database to PHP and then grow your own database on the PHP. Make the database do the work and prepare the data for you then you have less work to do. And also if you know this make your application or your plugin explode in the approach method and don't be afraid to make your own tables with the sensible. You should learn SQL but you should also learn what an index means and what a full table scan means because you want to avoid full table scans. That means that the database can't use in shortcuts to find the information requested but it needs to read one given that database then all of that is read into the CPU and process to find the information. You want to avoid those and you should have a group of databases or other shortcuts. So first I said learn SQL but next I said don't use it directly. Most of the time you should use the functions that are built into WordPress. So I'm quite sure how many of you have used the GetPosts function in the room or not. So use that for the basics and if that do what you want then you can go one step up in the chain and use the dbquery class. Here's an example you can do what complex query is for the dbquery class giving it as an array the parameter is what you want to fetch from the database and if that's not enough for you then you can use the dbdb instance and the functions get row and insert and so on. If you're not doing this standard queries and you really want to write your own custom SQL then the proper way to do that is to first run this dbdbprepare command and then give that the required parameters that are interactive in the user from other parts of the database and then send that to the dbdbquery. This helps you to avoid SGL injections. So this I'm just mentioning that all of these functions exist. Do use GetPosts for the most simple cases and then go up the kind of food chain and if none of the other ready made functions work for you only then go to the row dbdpprepare command. That's the correct way to call the database. Then the second thing you need to make sure is that the database server itself is correctly installed and configured. Because I've been involved in MariaDB I recommend that and I heard a lot of people don't like hard on the stewardship of my SQL that much. So most people recommend MariaDB and use a recent version at least 10.1 of the latest versions started at 10.3. So don't use any 5.5 versions of MariaDB today. Then inside the database you choose what storage engine you are using. So the version comes in a dbd and then there's hundreds of configuration options you can choose. One of the most important ones is the character set. You need to have UTF-MB4. So not UTF-8 but this UTF-MB4 so that you get support for images and then you need to set your collation correctly. So collation means that if you fetch for example the list of users from the database sorted by the last name then the database will do that for you. The sorting depends on what your collation is going to do. So if you have finished or finished collation then for example all of her will be at the end of the list but if you have an American collation then all of her and A will all be in the beginning of the list because in the American collation they are not considered characters at the end and then there's a huge amount of other settings regarding performance and reliability of your database to go through and if you don't want to do that, if you have a really big team and application you should probably hire a database expert or you should use a WordPress upkeep company or vice versa database preconfigured for you. That was eight tips about the database and my ninth tip is that you don't need to use the database all the time necessarily and Dimi already had a presentation of transients so here's a very simple code example slightly more simple than what Dimi showed maybe it's easier to grasp this one so you have a if in the beginning and you fetch your data from the transient and if the data was cached then your variable will contain the data and if it wasn't cached then the variable will be empty and this if will evaluate to false and then it will run the actual choir into the database and store the transient. Many of you are using transients already in your code how many of you will start using transients? The question is what does the transient actually do so it's a kind of a key value store so the functions are very simple you can set a transient you give it a name a value and then expiration time in this example it's 3600 seconds one hour and then you have a function to get the transient you just give it a new name and then you will get the value if it exists and then there's a third function to delete the transient and these are the simple versions then you have also the side transient function so if you don't have arrays then WordPress will put these in the database and it will actually go in the DB or just so if you have a really complex database wiring and you save the result and store it in the database but you have the result in the database and it's going to be much faster for you to just fetch the result from the database but even faster is if you store it in Redis which is a key value store Redis runs only from memory so it's very fast if that's available when you have an object of cash that PHP dropped in the WordPress collection then Redis will be used to make this response to the WordPress was this good enough so in this presentation I have links all these red all these yellow words all these words are links to the WordPress products and other documentation so you can find out the details this is just for making you aware that these things exist and then you can go and learn the details yourself then tip number 10 if you want to know what's happening in the database right now there's a command for it it's show process how many of you have used show process so you should go and check this someday if you have VPCLI on your website most of you hopefully have to just go there with SSH to your site then write VPDVCI then you have your console open and then you write show process is column semicolon and enter and it will show you the the quietness that are currently running and how long it took and you can run this command many times over you can get a feel of what are the motions what's happening in your database so this is very convenient this is real time and then in MySQL and already there's a feature called slow login which you can put on in the configuration and what it does then you can define a threshold for example 10 seconds and when you have a slow login on then already they log all inquiries that took more than 10 seconds in the log file and then you can go later and read that log file and investigate what are the slow ones and then assume those are some kind of bottlenecks and also if you are using a profiling system for example how we have an integration with timeways so if you can run it in production and get insight into what your php is doing it will hook into the into 1% of your traffic so that your site doesn't get slowed down and then analyze with 1% of the php executions and it will also the main focus for timeways is analyzing php but it will also show you information about your sql why are you using what your database is doing and then one extra thing to do in the middle so please don't push your development database into production we have cases where where the site is in production of you can log in with a user name in the very past and try to figure out a workflow that doesn't involve pushing your development database into production I know this is a bit hard because WordPress isn't designed from the beginning to be developer friendly in this sense that you can't just do an inclusion of your code and you wouldn't want it to be there because there's lots of plugins and teams are now settings are storming the database and then you have the outputs and so on so you have data in different places and you might be tempted to get everything correctly under development environment and then just pushing an identical copy into production but don't push whatever you just don't push all of your development database into production that's a risk factor to the extra ones so you probably know that in WordPress when you're writing a post it will do this all the same so you get these provisions and you can have tens of previous revisions of a blog post that's good for history you can go back but if you have this for all eternity it might fill out your database so here is the handy sql query to delete all of your post revisions that are older than 2018 you probably don't need your post revisions there's a second sql command to delete all of your transients from the database so transients is good and they're especially good if you're using race sometimes if you have lots of transients and they're all going to be options database then that might kind of shoot yourself in the leg because when the dp options they will be slow so with this command you can delete everything from that all the transients from the database and that's safe because those the day the transients that actually are still used to automatically regenerate next time ph2 runs define an expression type of rancid so he said that all the transients where you don't define an expression time will be stored in the database forever and they will be defined as an outlaw i can see that could cause lots of problems and then also people who are reading this team number 11 are probably in the process of trying to clean up their database from old profit and one way so where the transients have this uninstalled php sheet with them is if they're correctly written and that uninstalled php is supposed to trigger when you uninstall not when you deactivate but when you uninstall it or impermanently and it's supposed to be thin on and take the database whatever that plug in correctly the database and clean it up but sometimes that doesn't work or maybe you had the plug in at some time and you didn't have that time yet to have this time to install the php so what you can do that if you see that you have for example let's say you have your install anyone to be free of it and all of the stuff your database and you already uninstalled yours but you know that you're still having the database yours stuff so you can go to the source code and check out what's inside the uninstalled php and then just copy paste the sql command so that you can clean up your database who does how many of you knew that you can append the word explain to any sql command and you will get an explanation what's that what does it do good so once you've learned a little bit of sql and you know what the index is and the full table scan is then you can start using this this is a very simple example but if you have a longer sedent with many joints and so on then this explain will have multiple rows and it will give you all kinds of tips or explanations what the optimizer is doing when it's trying to get you the result of this query so then you can see if if indexes are used and what keys in those indexes are used and so what it means and then choose here you are talking about if you have a small site then the customer is probably small and doesn't have money to pay for optimization and this is slightly small but once you go into enterprise-grade WordPress sites then you will have a database and database performance and things so the phrase so in WordPress we have these pluggables and you can put this file called object-cash.bhp into the vp-content directory and then the built-in WordPress core will pick that up and call the functions in that file instead of using the database so that's the way how how our Redis tool speeds and you need to have Redis server on the server and then you need to have this file in your WordPress inspiration or our customer will do is about this argument and also related is some of you who are using Redis have one time noticed that in some cases it doesn't invalidate cash correctly and as I said earlier today is one of the biggest challenges in computer science how to correct and in some cases you if you do caching you might found probably it's not correctly invalidated but talking about this thing with objects not being invalidated correctly is a bug in the WordPress core and we locate where it is and if you have our server bugging installed on your site you will need to get that bug in the core and the Redis cache will work just our knowledge at the moment