Is it possible to have an indexed view in MySQL?

sql mysql views materialized-views indexed-view
Thomas Owens picture Thomas Owens · Oct 28, 2008 · Viewed 36.5k times · Source

I found a posting on the MySQL forums from 2005, but nothing more recent than that. Based on that, it's not possible. But a lot can change in 3-4 years.

What I'm looking for is a way to have an index over a view but have the table that is viewed remain unindexed. Indexing hurts the writing process and this table is written to quite frequently (to the point where indexing slows everything to a crawl). However, this lack of an index makes my queries painfully slow.

Answer

KernelM picture KernelM · Oct 28, 2008

I don't think MySQL supports materialized views which is what you would need, but it wouldn't help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would still cause the write speed issues.

Your best bet would probably be to create summary tables that get updated periodically.

Related questions

View's SELECT contains a subquery in the FROM clause

I have two tables and I need to create a view. The tables are: credit_orders(id, client_id, number_of_credits, payment_status) credit_usage(id, client_id, credits_used, date) I use the following query to do this. …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More