Ratheesh K J wrote:
Hello all,
I am required to analyze all the tables of our system. I need to know the key
parameters that should be taken into consideration for analysis. I am not speaking
about ANALYZE TABLE <tbl name>. I am required to manually look into all table
structures and pin point problems ( if any ).
So it would be helpful if i could know about
1) what exactly I have to look for in the tables.
2) Index management. Which fields are to be indexed and which not
Depends on the queries being run against the table. Don't index fields
you don't need to, otherwise you create a lot of overhead when
updating/deleting/inserting and take up a lot more space.
The basic guide is:
- index fields used in joins (eg select * a, b where a.id=b.id should
have indexes on both a.id and b.id)
- index fields used in where clauses
Enable the mysql general log and see what's really being run against the db.
3) How big can a table be? We have tables which have more than 50 lakhs of
rows. Any select queries, insert queries or update queries are taking more time
to execute. So what is a preferable table size.
How long is a piece of string? If you need to store lots of data, you
need to store lots of data.
If you have specific examples of where queries are taking a long time,
post an example and an 'explain' and someone might be able to help you.
4) Any other suggestions
Enable slow query logs and see what shows up. That will help tell you
about missing indexes.
Someone posted this site previously:
http://hackmysql.com/mysqlsla
Might help..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
