MySQL view performance

by

It’s about the view algorithm that’s been used.

The merge algorithm works well most table indexes and whatnot – the temptable algorithm doesn’t – in many cases your indexes will just be flat-out not used at all.

And there’s lots of crap that merge doesn’t support

MERGE cannot be used if the view
contains any of the following
constructs:

* Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
* DISTINCT
* GROUP BY
* HAVING
* LIMIT
* UNION or UNION ALL
* Subquery in the select list
* Refers only to literal values (in this case, there is no underlying table)

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. How can i optimize MySQL’s ORDER BY RAND() function?
  3. MYSQL OR vs IN performance
  4. Why does MYSQL higher LIMIT offset slow the query down?
  5. How to Perform an UPSERT so that I can use both new and old values in update part
  6. Is there a REAL performance difference between INT and VARCHAR primary keys?
  7. JDBC batch insert performance
  8. MySQL: Fastest way to count number of rows
  9. MySQL: View with Subquery in the FROM Clause Limitation
  10. Why is MySQL InnoDB insert so slow?
  11. MySQL vs MongoDB 1000 reads
  12. MySQL stored procedures use them or not to use them
  13. UUID performance in MySQL?
  14. MySQL “IN” operator performance on (large?) number of values
  15. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  16. Optimal MySQL settings for queries that deliver large amounts of data?
  17. What is better in MYSQL count(*) or count(1)?
  18. DOs and DONTs for Indexes [closed]
  19. MySql views performance [closed]
  20. MySQL performance: multiple tables vs. index on single table and partitions
  21. MySql Query: Select top 3 rows from table for each category
  22. “SELECT COUNT(*)” is slow, even with where clause
  23. Does Order of Fields of Multi-Column Index in MySQL Matter
  24. MySQL load data infile – acceleration?
  25. is php sort better than mysql “order by”?
  26. How can “set timestamp” be a slow query?
  27. Would it help to add index to BIGINT column in MySQL?
  28. ALTER TABLE ADD COLUMN takes a long time
  29. Disadvantages of quoting integers in a Mysql query?
  30. Optimizing MySQL for ALTER TABLE of InnoDB

Leave a Comment