MySQL Troubleshooting

Wrong Results from a SELECT

This is another frequent problem reported by users of an application who don’t see the updates they made, see them in the wrong order, or see something they don’t expect.

There are two main reasons for getting wrong results: something is wrong with your SELECT query, or the data in database differs from what you expect. I’ll start with the first case.

When I went over examples for this section, I had to either show some real-life examples or write my own toy cases. The real-life examples can be overwhelmingly large, but the toy cases wouldn’t be much help to you, because nobody writes such code. So I’ve chosen to use some typical real-life examples, but simplified them dramatically.

The first example involves a common user mistake when using huge joins. We will use Example 1-1, described in the previous section. This table contains my collection of MySQL features that cause common usage mistakes I deal with in MySQL Support. Each mistake has a row in the items table. I have another table of links to resources for information. Because there’s a many-to-many relationship between items and links, I also maintain an items_links table to tie them together. Here are the definitions of the items and items_links table (we don’t need links in this example):

mysql> DESC items; +-------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+----------------+ ...