10 Mysql query optimization good practices

How To Tutorials

Writing a good optimized sql queries will help to reduce server load on MySQL and also improves the load time and server response time for the web page.
Few tips to optimize MySQL queries are below:

1. Select only required colums:

Selecting every thing from the table will cause more memory uses and thus utilize more server resources.
BAD:
SELECT * FROM <TABLE>;

GOOD:

SELECT name, age FROM <TABLE>;

Only select required fields to have better performance.

2. Indexing correctly:

Index is not only for primary key, we should index  field that is search contineously, and also the joined column.

Example:

Select name, age from table where age=32 and name =’alex’ ;

Select name, age,group from table1 left join table2 on table1.groupid=table.group where age=32 and name=’alex’;

In first case, name and age should be indexed. ( Way of indexing will be covered in next blog).
In second case, along…

Ver la entrada original 462 palabras más

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s