Optimize index is effect in celerity search query data in big database.
First time, you must specific category who you want to get, for example :
SELECT NAME, ADDRESS, POST_CODE FROM MEMBER WERE POST_CODE=’01221’
from this example, just only one category “POST_CODE” for search data
You must to create index to get maximum speed for result search data, follow this script:
ALTER TABLE `MEMBER` ADD INDEX `IDXPOSTCODE` (`POST_CODE`) ;
Explaining
ALTER TABLE ‘TABLE_NAME’ ADD INDEX ‘INDEX_NAME’ (‘NAME_FIELD’);
Name Field here is clause will be search data.
This example also can be use in more clause category for search, but every clause you have to give different name for INDEX_NAME.
Example :
SELECT NAME, ADDRESS, POST_CODE,CITY FROM MEMBER WERE POST_CODE=’01221’ AND CITY=’PONO’
Above example, any more clause category. You just get add more fields in INDEX_NAME, example :
ALTER TABLE ‘MEMBER’ ADD INDEX ‘IDX_POSTCITY’ (‘POST_CODE’,’CITY’);
Position of fields name in Syntax create Index must be following position in “SELECT” statement like as field POST_CODE is first and then CITY.
This is just sort article, maybe useful for all.
