Let's talk about Magneto 2 or 1 MySQL database performance. Database performance in Magneto 2 is driven by pretty much the same thing that every MySql database is driven by, which is are you going to use an DB indexes to resolve your query or not? Don't switch Mysql Indexes with Magneto indexes. Basically Magneto indexes is a legacy stuff and is a bad development practices. Magento indexes is just a special cache located in MySQL and updated by cron. However complexity and performance overhead bigger than advantage of have such indexes. Instead you should use proper cache storage solution Like Elastic Search, MongoDB or Graph Database. Also you can save the data in de-normalized way directly on save instead of adding additional complexity.
I want to take you through the basics of indexing, and what it is and why it's so effective. If you look at MySQL or any other database, it will store its data rows in these large files on disk. There's no particular order for the documents on the disk. They can be anywhere in a database file. Also Mysql Uses in memory buffer for the database data. If you want to pull out a particular row, you do a query. Now, what
the database is going to have to do by default is scan through the entire table to find the data. This is called a table scan in relational databases.
It is death to your MySQL and Magento performance. If the data set is large, it'll be extremely slow. So, instead what we do is we create an index, or maybe more than one index. So how does indexing work? Well it's actually pretty straightforward. If something is ordered, like for instance, this list of product tags, even if it's very long, it's very quick to find something in the list because we can use binary search to do it.
So, finding something in a sorted list is really quick. Now to create an index, we want to specify a key. An index is simply an ordered list of keys. Now we don't actually keep them linearly ordered, we use something called a B Tree, but conceptually you can imagine it looking like this perfectly reasonable conceptual model. And if you are looking for something like say "t-shirt" tag, I can quickly search,
find the item and then have a pointer to the actual row that have this tag associated with them.
So this example actually represents an index that is
composed of just a tags. So we're using a single field as the basis for our index. But this is just a special case of the
more general idea of indexes in MySQL.
In MySQL,multi-column indexes are ordered lists of keys. You can have just one, as we saw in the previous example, or we
could do something like this and construct an index out
of three keys. Let's say for example the tag, the date on which a tag was created, and the username of the person creating the tag. The order is important here. Because,
conceptually, the way the index is built, is that the
tag will be at the top, so here are my hashtags:
So if you use tag I can go into the
index and find all the let's say t-shirt in this case.
And if you prefer to also have the date, then Mysql break it down and find all the t-shirt created on that particular day.
For this particular index, if you provide just the date, Mysql can't do much really with the index because the date is down here at this level. So to use an index, I need to start at the top, and this is true whether or not I'm doing a query or I'm doing an update or I'm doing a sort, because sorts also will use an index to sort their values. So for instance, if I pull a bunch of data out of the table and want to sort it by tag.
Now, one other point I want to make is that every time you want to insert something into the database this index would need to be updated, and that updating is going to take some time. So we use indexes to make Reads faster, but Writes become a little bit slower if you have an index because the index needs to be updated. So you need to take that into consideration when you're thinking about what indexes you might want for your particular application. Indexes are not costless. They take space on disks and Memory, they also take time to keep updated. So you don't want to have a index
on every single possible way you're going to query the table, you instead want to have an index on the ways you're most likely way you are going to query Magento collection.
Top comments (0)