Using MySQL views to increase performance

Let's assume I have a products table with deleted field. when a product is deleted, instead of deleting the actual record, I simply set deleted to 1. By doing this, I will have loads of data that I will not开发者_StackOverflow社区 need in day to day use. Will creating a view showing only not deleted products and query this view instead of products table increase the performance of my app?

Using a view does not dramatically increase performance.
However because the syntax of the view is fixed it will cache the result of your query after the first select.

However the result of the query is not indexed anymore, so if the view selects many rows and you do selects against the view you might actually slow things down.

Alternative
An alternative might be to move deleted product rows to a separate deleted_products table.
Or you can partition the table into a deleted and undeleted part.

Links:
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Unlikely. The view will be querying your table anyway.

It could however make development of your application a bit easier.

You are describing performing a soft delete and asking about performance for SELECTs against a given table. I agree with other posters that your performance gain here on the SELECT will be minimal, but for your overall system may be better with this strategy since DELETEs are expensive. If you are doing a lot of deletes, doing a soft delete, then periodic cleanup will improve overall performance.