What level of MySQL/MariaDB does an average php programmer need to know?

Question

MySQL

I plan to change jobs soon and decided to prepare. Unfortunately, at the moment, 95% of my work with the database is migrations and ActiveRecord. The level of all my sites is CRUD. In one project, I even called stored procedures, but never wrote my own :)
My current level:
1) the basics (insert, update, delete, create table, join).
2) I seem to be able to create an adequate normalized base, put the necessary FK, indexes.
3) I don't really understand how to work with expain. More precisely, I know what this command does, but I don’t know how to work with the result of its action correctly. How to optimize queries.
4) never dealt with replication, sharding, partitioning. What engines and when to use. Always InnoDB.
But, nevertheless, I am counting on the position of middle and therefore I want to prepare for interviews. What will they ask? What literature to read and practice?
PS: The fear is also caused by the fact that at one of the previous interviews they began to ask me what PK and indices are. Well, I answered in general. Then, he began to say that this is not enough, you need to know how it works at the file level and almost the source code of the subd. Is it really, I need to know such subtleties?

Answer 1

For the middle, you only need to pull up point 3. Sveta Smirnova has several excellent presentations on working with explain, they are easily found by Google.
It is also desirable to distinguish between transactions and locks, when to use which.
About the device at the file level - I think these are additional questions. At the interview, they always ask questions that do not affect the suitability of the position, but simply add touches to the portrait of the interviewee. In response to such a question, one can honestly say that I didn’t dig so deeply, and just try to speculate on the topic of how it can be arranged.
In general, in my experience, you should not overestimate the results of the interview. When I went to interviews 5 years ago, after two of them, which I myself considered failed, they sent me job offers.

Answer 2

PS: The fear is also caused by the fact that at one of the previous interviews they began to ask me what PK and indices are. Well, I answered in general. Then, he began to say that this is not enough, you need to know how it works at the file level and almost the source code of the subd. Is it really, I need to know such subtleties?

There is no clear division into "Jun/Mid/Sen" and cannot be by definition. There are so many technologies that it is impossible for anyone to know them all. In addition, technologies are developing very quickly... Accordingly, there are also many companies and they also have many requirements for the position. Anyone who is currently working as a lead developer in an office like Sharazhmontazhsaytprodazh LLC (conditionally) is unlikely to be able to qualify for at least a middle position in some serious, reputable company.

Answer 3

The average programmer needs to have an understanding anyway. You need to understand aggregates, write queries from memory, understand relationships and all kinds of interests, be able to deal with transactions and understand how the query analyzer works. It is necessary to be able to explain, but it is not necessary to know it thoroughly - the main thing is to be able to draw conclusions.
Most likely, in that company, a more qualified developer is needed for this position. Well, or they wanted to probe your current ceiling