I wonder how many people are already aware of this, and practice it? I wonder if MySQL is capable of this?

Constraints are a way of enforcing data integrity. If your data is of value, they prevent it from going corrupt, becoming untrustworthy. There are different types, from default values to checks to referential integrity. All of these are declarative - you tell the database what the rules of proper data are, and it makes sure they're obeyed.

A check constraint is simple. If you have an employee table with an age column, you might only allow values from 18 to 80 years old. If somebody tries to update an employee and make her 175 years old, the database would fail that update - if you have a check constraint in place. Here's the kicker, if you query the employee table for anyone > 80 years old, it will return an empty set without even looking at the table!

DRI is similar. If there's a primary and foreign key relationship between two tables, you can't have data in one unless there's matching data in the other. With this setup, if you query for line items for an order that doesn't exist, the database will return an empty set, often by looking at the order table instead of order detail. Why? The table is much smaller, so you get your answer much faster.

You can verify this by looking at query plans. And then, so long as you don't use nocheck, you can optimize read performance in your database by setting up proper constraints.

MySQL has had constraints since ver 3.23, but only if you use InnoDB tables

But does the MySQL query optimizer consider (trusted) constraints to narrow the execution plan?

And are you serious - a database engine really only has something as central as constraints with one particular storage type?

And are you serious

Yep, but to be fair to the MySQL developers, the MyISAM tables were/are only meant for relatively simple and basic applications, where all the bells and whistles were not really needed but speed is a concern.

If the database design or the application of the database called for the more "advanced" features then the storage engine of choice should be InnoDB, or change servers to PostgreSQL.

Wow. I learn something every time I log in here. Anyway, my point wasn't to bash MySQL. I'm grateful for the knowledge I pick up, but I was actually trying to give some of that back, instead of just troll.

Does anybody know whether the MySQL query optimizer uses constraints (check, PK & FK, etc) to make better decisions about how and in what order it will execute the query?

Any type of constraint is going to slightly slow down insert and update queries, and we've had several data warehouse projects where we disable them (especially DRI) to improve ETL performance. That's a special situation, because foreign keys are implied in a DW. But perf can arguably be a reason not to constrain your data, if you have some other validation method in place. But, if those constriants give the query optimizer more ways to narrow the work that needs to be done to satisfy a query, there are specific cases where they can give substantial performance improvements. So I was hoping to spark a discussion about when it makes sense to use them, in this context?