The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may hit when designing schemas and queries, but then I thought it could be a good idea to publish this on the blog as well.
To demonstrate the issue let’s use a typical example – a sales query. Our data is a tiny store directory consisting of three very simple tables:
PLAIN TEXT SQL:
- CREATE TABLE `products` (
- `prd_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `prd_name` varchar(32) NOT NULL,
- PRIMARY KEY (`prd_id`),
- KEY …