Re: Speed Enhancements - Indicies

[Peter Brawley]

> I think I am using InnoDB, what do you mean?

myisam_sort_buffer_size has no effect on InnoDB tables.

In my.ini, all innodb settings are commented out, you need to fix that.

The crucial one is innodb_buffer_pool_size, which should be 60-80% of RAM available to MySQL; innodb_log_file_size should be about 25% of innodb_buffer_pool_size.

Once that's set and mysql restarted, to optimise the query, turn the query cache off. Once it's optimised, test whether caching speeds up or slows down performance (don't be surprised if it slows thing down).

With caching off, what's the performance diff between the View and the underlying query?

Let's see the result of Explain Extended on the query (not the View), putting the result inside BBCode code tags so we can read it.