ansaurus

Question

explain command usage

Answer 1

+1 A:

Key - The key column indicates the key (index) that MySQL actually decided to use. The key is NULL if no index was chosen. To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query

From your query result we can see that you have no Index defined for your table because possible_keys column is also NULL.

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.

Take a look at this page to see the syntax used to create an index in MySQL:

CREATE INDEX Syntax

This page details each column of the explain plan:

Looking at the MySQL Explain Plan

Leniel Macaferi 2010-07-20 14:11:06

@Leniel Macaferi - but i have a primary field in my table . does that not create a index automatically for that field. or do i need to explicitly specify index?

pradeep 2010-07-20 14:31:48

You need to explicitly specify/create the index. That's true. No matter if you already have a primary key defined. The concepts of primary key and index are different things.

Leniel Macaferi 2010-07-20 14:40:12

@Leniel Macaferi -alter table tablename add index (columnname ) will do it rite

pradeep 2010-07-20 14:53:19

CREATE INDEX indexname ON tablename (columnname)

Leniel Macaferi 2010-07-20 15:00:44

@Leniel Macaferi - basically on what column we need to create a index?so you say that when i create a primary key/foreign key index is not created,bcos i had read that its created.see i have 2 columns like alias and product .alias is the name of the products like samsung and all.and product refers to actual product like mobile,camera. i have created a index on alias column now. but i my where clause i use like where product = 'mobile' . is this okie. will the search be faster?

pradeep 2010-07-20 15:08:24

Generally you'd create and index for a column that will be used most in the where part of your query. In this case you specified that you're going to use the product column in your where clause. So it makes sense to create an index for the product column instead of the alias column. You'll perceive difference on search time only and only if your table has lots of rows. Otherwise you'll not note the difference at all.

Leniel Macaferi 2010-07-20 15:18:01

okie i created a index on product column so now explain command says like id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE products ref product_name_index product_name_index 902 const 147 Using whereso now its okie rite.number of rows searched to get the result is also less

pradeep 2010-07-20 15:30:05

That's it pradeep! :)

Leniel Macaferi 2010-07-20 15:46:11

ansaurus

tags:

views:

answers:

explain command usage

related questions