Optimisation and Performance Mysql , Zend Framework 2 and Doctrine 2? i'm facing some problem with performance in an application using zend framework2 and doctrine 2. i would like to optimis it but i don't know from where to start , i read some many questions/ answers about performance and optimisation and i got using cache and indexation But my problem is where exactly apply the indexation is on doctrine or on mysql ? the cache where on doctrine or zend cache or the both ?? i can't figure out from where to start espacially that there a high traffic on the application so also the cache might not be the right solution !!!
plz any help or idea would be appreciated.
Regards.
Thanks
Start with the database and work your way up. Check out the queries you run using the command line before sending them through a framework. You can activate the general log to check every query that's sent to the database if you want to check exactly what, exactly, is arriving at your server after having used the framework. Frameworks can be good (relieving you of tedious chores), but they can be the source of (unpleasant) surprises.

You don't mention what version of MySQL you are using, but if 5.5 and above, you can make use of the Performance Schema (P_S). The P_S is MySQL's (embryonic) implementation of an Oracle Wait Interface style system for instrumenting the database. It tells you where time is being spent and this allows you to concentrate on the major problems and address them with hardware (if necessary - i.e. more/better RAID if disk-bound) or query tuning (if one/some queries are taking a lot of time). You should try and use at least 5.6 for this (is there any reason not to?).

If you're having problems with specific queries, you can post back here (new question) with DDL (SHOW CREATE TABLE my_table) and sample data (INSERT INTO my_table VALUES(...)). You can use SQLFiddle and even provide a download (you can hash sensitive data).

Sorry not to be able to provide anything more specific, but your question is quite broad and general - as I say, when you run into specific issues, post back here and we might be able to help.

[EDIT in response to OP's comment]

I'm assuming that this doctrine framework generates queries, but the answer is no, you won't have to generate indexes through the framework. My experience (not huge) with frameworks is that they will generate queries which give you the data you want. 

The indexes are normally left up to the developer because many/most/nearly all of these frameworks are designed to work with multiple RDBMSs (this is not necessarily a good thing!), so they do the bare minimum (some would say the lowest common denominator) of SELECT FROM, INSERT INTO and UPDATE which is fairly standard SQL. However, the syntax for creating indexes varies from server to server (as for many other things), so the frameworks in general don't handle this well.

Ensure that your framework is giving you the correct data, then see if adding indexes can help speed up your particular application, because no framework can possibly anticipate all use cases (table sizes, RAM, my.cnf... the list goes on).

All I can say is that now if you're having a problem with a particular query, post a new question here giving the details I mentioned. Check the performance of the framework generated query and then check with your own choice of indexes. Don't forget that a system is made up of many queries and that modifying one may adversely affect others. Indexes can provide enormous benefits, but they do have downsides such as slowing down UPDATEs and INSERTs. Ideally, after the first stages above, you should have some sort of system for replaying your new queries with a simulated system load - see here. Good luck with your system.