How To Optimize MySQL Queries Anderson 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

Softnet Solutions Inc
(803) 325-1110
454 Anderson Rd S
Rock Hill, SC
Tiba Solutions Llc
(864) 679-4800
201 Brookfield Pkwy
Greenville, SC
Professional Software Associates
(864) 288-6798
4115 E North St
Greenville, SC
Electronics Boutique
(843) 669-0272
2701 David H McLeod Blvd
Florence, SC
Speedbuilder Systems Llc
(803) 647-9532
Columbia, SC
Cirriculum Advantage
(803) 749-0744
421 Cressfell Rd
Irmo, SC
Application Computer Systems Inc
(864) 292-5980
1 Alford Ct
Greenville, SC
Internet Services of the Low Country
(803) 943-0774
104 Elm St E
Hampton, SC
Electronics Boutique
(803) 366-1807
2301 Dave Lyle Blvd
Rock Hill, SC
Mainstay Technologies
(864) 288-4940
184 Commerce Ctr
Greenville, 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

Preferred Systems

Contact Preferred Systems for all your computer and network Support Needs in Greenville.

8642336395
191 Inglewood Way
Greenville, SC
http://www.preferred-systems.com

Preferred Systems provides network support in Greenville SC and the Upstate.

We specialize in supporting small and mid-size business with all their IT needs. We support Microsoft Windows 2000 Server, Windows 2003 Server, and Windows 2008 server.

We support destop PC's running any version of Windows.

We can assist your company with the latest technologies including virtualization, such as VMware server, VMware ESX, VMware workstation, Microsoft Virtual PC, and Microsoft Virtual Server.

Please contact us for all your computer support needs in Greenville.

Related Articles
- Business Blog Savvy Anderson SC
You can pat yourself on the back if you’re one of the thousands of small business professionals that have set up a blog to communicate with your potential clients. Or, maybe you haven’t started your business blog yet, but intend to.
- Traveling Exhibit Promotes Independent Inventions Anderson SC
- Getting Your Own Dotproject Anderson SC
- iPhone Security Info Anderson SC
- Extreme Energy Makeover Anderson SC
- Application Security DbProtect Anderson SC
- Top Multiplatform Deployment Tools Anderson SC
- IT Skills Anderson SC
- Affordable Web Hosting Anderson SC
- Spam Filters Anderson SC
Related Articles
- Business Blog Savvy Anderson SC
You can pat yourself on the back if you’re one of the thousands of small business professionals that have set up a blog to communicate with your potential clients. Or, maybe you haven’t started your business blog yet, but intend to.
- Traveling Exhibit Promotes Independent Inventions Anderson SC
- Getting Your Own Dotproject Anderson SC
- iPhone Security Info Anderson SC
- Extreme Energy Makeover Anderson SC
- Application Security DbProtect Anderson SC
- Top Multiplatform Deployment Tools Anderson SC
- IT Skills Anderson SC
- Affordable Web Hosting Anderson SC
- Spam Filters Anderson 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