"Āngelo M. Rigo" <[EMAIL PROTECTED]> wrote on 01/27/2005 03:04:15 PM:
> Hi > > I have an aplication wich is opening to many connections even i am > using persistent connectins and closing every connection i do open > > I have created indexes in all the fields i supose they are needed > > I would like to know if i can and how can i measure where indexes > are needed or where they can open too many internal connections > since my aplication does many searches through the database.. > > Thank“s in advance > > > __________________________________________________ > Converse com seus amigos em tempo real com o Yahoo! Messenger > http://br.download.yahoo.com/messenger/ MySQL typically only uses a single index per query. So if you have indexed each column individually, you are probably not as well-indexed as you think. My suggestions: 1) Turn on the slow query log (http://dev.mysql.com/doc/mysql/pt/slow-query-log.html) 2) Actually look at the queries captured in the slow query log. Look for things that frequently appear in the WHERE, GROUP BY, and ORDER BY clauses. Use the EXPLAIN command to analyze your current index usage for those queries that end up in the slow query log. (http://dev.mysql.com/doc/mysql/pt/explain.html) 3) Replace most of your single-column indexes with appropriate multi-column indexes. (http://dev.mysql.com/doc/mysql/pt/mysql-indexes.html) 4) Use EXPLAIN again to analyze your slow queries against your new indexes. 5) Repeat from 2) until things improve enough. Then turn off the slow query log. The manual is your friend. Use it often: (http://dev.mysql.com/doc/mysql/pt/index.html). I have no idea why the Portuguese version does not have a "Search" function on it like the English version does. (Maybe that index is still under construction.....????) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
