Optimizing queries in MySQL

[Caden Fernandes]

Hello sir,

I am running with windows operating and the one of the popular database management system is installed in my system and the data is being accessed in a small network.Whenever,the database get starts and users gets ready to access the database,they complained that the record fetching is taking so much time to accessed and when I need to investigate the errors the database gets hang.Why and what's going on,i am not getting anything.

Thanks.

[Praetor]

The proper management of database is being done that's why the database is occurring this type of error and unavailability.

There are so many reasons can be caused to degradation the performance of database.It doesn't matter that which one operating system you are using and what database you are using.

The proper optimization of database is going on.You need to perform the tuning of your database.The tuning of the database contains different phases in which you can increase the performance of database and availability also.

[Katty]

There are some points which causes the matter to decrease the performance of the database,The action which you can perform to tune your database.

Overall,here is some points you need to care about-

1- Creation of indexes and management.
2- User management and query performed by the user must be trained user for the database.
3- The proper management of network is necessary where the database is being retrieved.
4- The Instance memory(Memory management) should be blown in proper way.

These are the primary things to manage your database,To tune a database the DBA should be qualified to do this.

[kelfro]

Managing FULL scan of table

The management of table is very necessary because most of the data is accessed from this object near over all data is accessed from this object.

If a table contains 1 Lac of rows and you queried on this then the server process will make a full scan of the table because there is no index is defined from this table.

If you going to search a particular record of the database like one of the last row of the table then the server process will start an operation to search the record exist in the table and will scan whole table for finding a particular record of the table.

An index can remove the full table scan operation.For this you need to create the index on the table .

[Zecho]

creating Index in MySQL database

This is the syntax described here to create an Index in MySQL database for a particular table through which the query can be able tom access only the record which needs to be accessed according to description of the Index.

This is the syntax to create the Index -

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name