Using view to pre-sort a selection in mysql?

I have a table a with two fields, say value1 and value2.

Every single selection I will then make on this table is sorted on value2 DESC, but since the table is rather large I can't do it "vanilla" anymore due to performance issue.

My solution was to create a view v which was basically something like:

CREATE VIEW v AS SELECT * FROM a ORDER BY value2 DESC

My questions now are:

• Is it a correct way of doing it ? Will the sort indeed be done once for my view instead of every time I access it ? And if not, how should I proceed instead ?
• How do I then select data from it while making sure the sort stays 开发者_如何学JAVAas I want, something like "SELECT * FROM v LIMIT 5" ? Or do I have to respecify the order by clause when reading from the view (wouldn't it discard the pre-sort and redo it) ?

Thanks for any help !

---

Will the sort indeed be done once for my view instead of every time I access it?

No, a non-materialized view (MySQL doesn't support materialized views) does not cache data -- they are executed for every reference.

How do I then select data from it while making sure the sort stays as I want, something like "SELECT * FROM v LIMIT 5" ? Or do I have to respecify the order by clause when reading from the view (wouldn't it discard the pre-sort and redo it) ?

This is a prime example of why ORDER BY should never be included in views.

Within the view, the ORDER BY is encapsulated. Meaning, you can't see the ORDER BY and will likely define another ORDER BY on the view reference. The ORDER BY can't be pushed as a predicate to the inner query, so the database will run the query the view represents (including the ORDER BY). In this:

CREATE VIEW your_view AS
  SELECT * FROM YOUR_TABLE ORDER BY value2;

  SELECT *
    FROM your_view
ORDER BY value1;

...the outer ORDER BY will be applied -- you've compounded your performance issue, rather than solve it.

ORDER BY is a necessary evil, because there's no other way to guarantee order. Eli's solution to use the MySQL only ALTER TABLE ... ORDER BY is worth trying:

ALTER TABLE a ORDER BY value2 DESC;

...but according to the docs it has to be re-run after INSERT/UPDATE/DELETE statements are run.

Conclusion

Be careful about using ORDER BY in views, if at all. If performance is really an issue, review indexing and consider table partitioning.

---

If you want the whole table to be sorted by "value 2" in DESC order everytime, why not do this?

ALTER TABLE `table_name` ORDER BY `value2` DESC;

---

Is it a correct way of doing it ? Will the sort indeed be done once for my view instead of every time I access it?

No

And if not, how should I proceed instead ?

Start off by creating a index on value2

How do I then select data from it while making sure the sort stays as I want, something like "SELECT * FROM v LIMIT 5" ?

Yes, Unless the query has its own ORDER BY, the ORDER BY from the view will be used.