Five problems on PHP and databases, and their solutions

There are five problems and their solutions commonly found on databases operated from PHP on IBM's site.

This is a commentary on commonly occurring problems when designing databases.

So, what are the five main problems?
Five common PHP database problems

The first is that the old PHP code directly accesses the database. An alternative to this is to use PEAR 's DB module, or to use PHP data objects, PDO classes.

The second is not to use auto increment. Basically, MySQL automatically increments a unique ID for each record, but the pattern that it does not utilize it. If you do not use auto increment effectively, it seems that not only it is inefficient but also the load will be high. To use the autoincrement of MySQL, not to mention the solution.

The third is to multiplex the database. Although it tends to be multiplexed thought that it is better to divide if it is out of place and a huge database, it is not necessary under normal conditions. Also, rather than running a number of search queries, if it is possible to do with a single query even if the code becomes longer, it seems that there is no problem with a huge database. At first glance about this point there is such a thing! A huge database is written in every book as soon as it is divided! Although it tends to tend to think that it is not such a thing when actually trying it.

As for the third point above, GIGAZINE certainly does so, and it is much lower loading and decreasing speed when dividing the number of queries than dividing the database etc. MySQL is better than imagined in that respect.

How much level you actually need to divide is about the level of mixi. The following article is helpful.

Mixi's CTO says "How did mixi deal with increasing traffic?": ITpro

The way it looks is as follows.

[ThinkIT] 3rd: Scale out of database (second half) (1/3)

Also, in the case of a regular MySQL database, it seems quite nice to use what I can apply from any point on the page below.

MySQL 4.1 Reference Manual :: 5.2.13 Other Optimization Tips

The fourth problem is not to use relations. Relational databases are not like programming languages. For example, there is no array (array). Use one-to-many relations instead. This allows you to do the same thing as an array. In other words, the problem is that you are bothering to create arrays with programming languages. There is a sample code on IBM's site, but in short it is slow, difficult to maintain, and it will create something that can not fully exploit the merits of the database. The only solution is to rework the schema by taking over the traditional relation technique.

The fifth and last problem is called "n + 1" pattern. When you search the list of entities, you will search one by one to get details of each entity. This is called "n + 1" pattern. Because quite a lot of queries will run. Search the list of all entities with one query. Also do the same thing with the next published query. It is done for all n entities, but if n is 10 or so, it will still collapse at a stretch if it becomes 1000 or so. There is an example of a bad code on the IBM site, but it is a clean code at first glance. However, it is inefficient. In other words, reduce the number of running queries to improve efficiency. Although this directly leads to performance, it is said that there are many cases where it is unexpected, often noticed.

It seems that all the above five, all basic things. If you design the database so that you can operate the database appropriately from PHP without understanding this, it seems that it will be remarkably poor performance though it is moving.

Also, what is a database? I think that it is very difficult to explain to someone, but I think that if you read the following page you will understand to some extent.

Relational database overview