Database problems in MySQL/PHP Applications

I know – old post but I just felt I had to speak my mind on this one…

> 1. Using mysql_* functions directly This is probably bad but I do not like
> solutions proposed by original article ether. PEAR is slow as well as
> other complex conectors. I have not yet tested PDO but would not
> expect it to beat MySQLi in speed. It is however bad idea to use
> mysql_ functions directly as well – I would go for using mysqli object
> approach. The great things about objects is you can easily overload
> methods and get debugging and profiling tools, as well as have
> tools which protect you from SQL Injections.

I agree – regardless of what database is used, abstracting out core functionality usually means more portable code. You’re right that when used properly, prepare can (if used properly) help increase security against sql injection.

> For example I have little wrapper which allows to do
> $dbcon->query(“Select email from user where name=%s”,$name) –
> wrapper will detect query is being called with multiple parameters
> and will perform needed checks and query rewriting. You also can
> use pretty much direct path to mysqli extension to performance
> critical queries if you need.

Nice but using prepare/execute is more consistent with what I’ve seen others do. Then again, having your own wrapper function to handle the translations isn’t a bad idea either. I’m not suggesting your way is wrong, but it’s not as common.

> I would also note for many PHP applications abstraction layer is
> not the main performance problem, also benefit from persistent
> connections can be much more modest. DVD Store was special
> type of application which was designed to have very simple logic
> besides database – in most cases you would have beautiful page
> rendering as well as much more queries per page which will make
> performance improvement much smaller. Notable exception being
> AJAX applications which may do very little work and formating, so
> database connection may become the issue. Caching should be
> good help in this case though.

95% of the time – yes – I agree. Learning to use query and external caching of result sets is generally a good idea and having pre-compiled summary data ready to go in the database also helps depending on the needs of the application.

> 2. Not using auto_increment functionality This is right. With some
> exception however. For example Innodb tables do internal full table
> lock if auto_increment is used so using values generated elseware
> might be faster.

I disagree with you on this completely because InnoDB uses a clustered index based on (in order) the primary key you specify (an auto_increment), a unique key, or one it creates for you. It’s been my experience that failing to use a non-conctextual primary key (NCPK – an auto_increment column) in nearly (emphasize nearly) every InnoDB table can have very serious and negative performance implications. Remember that if you don’t create either a unique key or a column with an auto_increment primary key in a table using InnoDB, the database will create one for you that you can’t use or see. I’ve also seen others try to make InnoDB use a composite UNIQUE key on a table as a primary key taking up far more space than even a BIGINT UNSIGNED will and gaining no benefit from it. I’ve seen developers ask the database to do row lookups by multiple columns of varchars is likely. Don’t do it. I can think of very, very few cases when I would even think about not using a NCPK and most of those demand using some other storage engine like Archive…

> 3. Using multiple databases Honestly I do not see application using
> one database per table that often. I however often see applications
> using multiple databases to group tables by certain logic, such as
> you do with directories to group files. I think this makes a lot of
> sense. Sometimes grouping is done so a lot of databases are
> needed – for example if grouping is done by user. This might be a
> bit extreem if you have thousands of users – I would rather do
> many to many relationship between users and tables but it also
> might work.

Having a logical separation of data sets helps document relationships of data. There is no reason why a query can’t reference data in two separate databases in the same instance of MySQL. If nothing else, it makes replicating a subset of the data much easier.

> Regarding if you use many tables you’re doing something wrong it
> is frequently told by people with traditional database background.
> Things are different with MySQL.

Good design will help dictate how many tables are required in order to get the job done. To be good at designing an application, the developer will be wise to consider not only what needs to be stored, but how the data will be retrieved and how it will be used. What kind of summary data will be required? What kind of questions will the application ask of the database?

> There are many successful applications, using tens of thousands
> of tables per host and archiving great performance by doing so.
> Using multiple tables gives some very important benefits – your data
> becomes managable, your ALTER TABLE or OPTIMIZE TABLE
> now locks small table for few seconds rather than giant 100GB table
> for few hours so can be done pretty much online. You also get good
> data clustering so table becomes hot very quickly due to data locality
> once this user starts his queries. It is also much easier to do backup
> and restore if you need only portion of your data recovered.

While this is true if the goal is to restore a part of the data set, when it’s time to do a full data restore, more tables means longer restore times. Deciding to create a new table for a part of the data set is not a black or white thing. It involves understanding how the data will be saved and used.

> 4. Not using relations This one is right one but also with the catch. It
> is very traditional recommendation to normalize your data however it
> does not always bring good performance. Joins are expensive and
> you can often do much better with denormalized data. You may wish
> to use denormalized data as cached lookup table however so you do
> not have all these problems with loosing data etc. Read more in my
> Why MySQL Could be slow with Large Tables article.

Deciding whether or not to let the database maintain referential integrity is just one of the factors that developers should consider when deciding when / how to create indexes. Foreign key references may add processing time to inserts, updates, and deletes but there are other benefits. Also – if you’re replicating from a master to a slave, there is no reason why you can’t remove the foreign key references on the slave(s) as long as you are certain that all the changes are being made on the master and all those changes are making it to the slave(s).

Like any other decision in database design, one must consider the cost of the benefit versus the value added by having the benefit available. Making a blanket statement that using relations is bad is unwise.

> 5. The n+1 pattern This probably should rather be called Not using
> Join. This is typical error. On other hand in MySQL you might be
> better of using several queries than doing complicated ones. Of
> course you would rather use IN() than do 100 of queries in this case.
> This most applies to subqueries Where Subselects with IN() become
> corellated even if they are not, and so using IN() list of values derived
> by previous query. For example you can do:

> SELECT id FROM users WHERE featured=1;

> Now populate List for IN on your PHP application:

> SELECT * FROM articles WHERE user_id IN(23,545,654,34)

> instead of:

> SELECT * FROM articles WHERE user_id IN (SELECT id FROM users WHERE featured=1)

Ouch! Use INNER JOIN for this instead because it allows the optimizer to use the indexes to join rows together, potentially preventing a table scan. The trick is to know what kind of JOIN to use so that the right number of rows are scanned and returned.

SELECT * FROM articles INNER JOIN users ON users.id = articles.user_id AND users.featured = 1 ;

> Use Indexes This item was not in original article, however I think this
> is the most common mistake and it is very important to fix it. Most
> applications I have to fix have number of indexing missing which
> requires queries to do full table scans. Funny enough this is often
> not the problem in the beginning – if application is bought or custom
> ordered it frequently can pass customer QA – it will work quite fast
> with almost empty database. With database growth it will however start
> to crawl.
> So developing you PHP applications use test database with reasonable
> amount of data in it. And do run EXPLAIN for your queries, especially
> if you see them in slow query log. If you have trouble understanding
> EXPLAIN or optimizing your queries remember
> we’re here to help.

Partial agreement: “Use Indexes smartly.” Indexing columns that have very low cardinality will often be a waste of resources because the optimizer just won’t use them. Cardinality can be thought of as “how unique or common is what I’m looking for in this table?” An index that has very high cardinality (as high as the number of rows in the table) are potentially very valuable if searches are done against that information. On the other hand, an index that has very low cardinality (1/2 a million rows with an index on a Boolean column for example) is unlikely to be helpful unless you know your data well enough to know you’re looking for rare occurrences of the “weird” setting of the column’s possibilities. Also, indexing the full width of very wide columns will take up tremendous amounts of memory and disk. Because indexes are fast by storing them as fixed-width data (think of converting your varchars to chars for example), then having to do byte comparisons across the full width every time and you understand why I am concerned, especially for composite indexes.