How To Optimize MySQL Queries Aiken SC

MySQL is a relational database management system, or a program that allows several users to access a number of databases. Learn about MySQL queries and how to optimize them in this article.

Local Companies

OS Solutions
888-275-4893
903 Broad Street
Augusta, GA
Eb Games #5177
(803) 641-2321
2551 Whiskey Rd
Aiken, SC
Tegron
(803) 644-8308
2210 Woodside Executive C
Aiken, SC
Bcs-Bradshaw Consulting Services Inc
(803) 641-0960
2170 Woodside Executive C
Aiken, SC
Computer Services
(803) 641-7060
120 Pendleton St NW
Aiken, SC
Gateway Gaming Llc
(864) 422-2396
1243 Piedmont Hwy
Piedmont, SC
Logic Junction
(843) 971-2989
217 Lucas St Ste L
Mount Pleasant, SC
Perez Software Service Inc
(843) 553-9993
100 Hamlet Cir
Goose Creek, SC
Systems & Services Llc
(843) 552-0819
7271 Investment Dr
North Charleston, SC

Using indexes

Using indexes makes the query faster in the same way as an index in a book helps in looking for a particular word.

To know how and which indexes are used in a given query, use EXPLAIN command as a prefix to select query.

EXPLAIN SELECT emp_id FROM ORGN_DATA WHERE dept_code = 'IT';

This command returns following information:

Table - Which table(s) are used in the query
Type - JOIN type. Values can be system, const, eq_ref, ref, range, index, all
Possible Keys - All keys which can be used for indexes
Key - The key actually used for index
Key Length - Shorter keys are better
Ref - Other Columns used with key to get the results
Rows - Number of rows from where data will come
Extra - Extra information. Some possible values can be using index, using where, using temporary, using filesort

You should reconsider your table structure if Possible Keys contain NULL value.
A NULL value in Key field indicates no index is being used. Using no indexes will deter the performance if there are too many records in the table because, for any query, all the records will be searched for the given condition.

Use ALTER command to add index as given below:

ALTER TABLE table_name ADD INDEX (column_name);

You can do indexing on multiple columns using:

ALTER TABLE table_name ADD INDEX (column_name1,..,column_nameN);

MySQL uses leftmost prefixing, so automatically indexes will be added for column_name1, (column_name1,column_name2),...(column_name1,..,column_NameN-1). This is quite helpful in situations like searching based on surname so an index on (surname, firstname, middlename) will automatically add multiple indexes used in faster querying.

Points to remember:

Featured Local Company

OS Solutions

888-275-4893
903 Broad Street
Augusta, GA

Related Articles
- 3G Wireless Capacities Aiken SC
We all pack appropriate clothing when we leave for a business trip, but most mobile users will still feel naked if they don't have easy, fast access to the Internet.
- About Fans Aiken SC
- A guide to Windows Home Server Aiken SC
- A Sentinel to Screen Phone Calls Aiken SC
- About VoIP Aiken SC
- Digital Photography Equipment Aiken SC
- A Tiny Silicon Clock Aiken SC
- Acer-Gateway Deal Aiken SC
- Accessing the Apple TV Settings Aiken SC
Related Articles
- 3G Wireless Capacities Aiken SC
We all pack appropriate clothing when we leave for a business trip, but most mobile users will still feel naked if they don't have easy, fast access to the Internet.
- About Fans Aiken SC
- A guide to Windows Home Server Aiken SC
- A Sentinel to Screen Phone Calls Aiken SC
- About VoIP Aiken SC
- Digital Photography Equipment Aiken SC
- A Tiny Silicon Clock Aiken SC
- Acer-Gateway Deal Aiken SC
- Accessing the Apple TV Settings Aiken SC

Rss   Delicious   Digg   Add To My Yahoo   Add To My Google   Bookmark   Search Plugin

Topics:
Advertising Family Home Services Real Estate Resources
Business Services Fashion Industrial Goods & Services Retail & Consumer Services
Career Financial Services Insurance Software
Cars Food & Beverage Internet Technology
Computer Hardware Franchise Legal Telecommunications
Construction Health Miscellaneous Trade Shows
Education Holidays Nightlife Travel
Entertainment Home Appliances Online Database Weddings
Environmental Home Electronics Pets World History