Why and how to use a index?

[Anonymous]

This question is rather none specific. I need to know what is the significance and usage of index in tables. What are the differences between the primary key and an index and where and how one should use these?
Thanks

 

[Anonymous]

hey.... this came from my indonesian reference book.... it says that index usually use the integer data type while primary key doesn't always use integer data type, for example :
table of employee usually have at least this to column :
ID_Employee (Primary Key, Integer, auto_increment, not null)
Name (Varchar(40), not null)
while table for user (usually for web forum) have the "Name" column with (Primary Key, Varchar(40), not null)
hope it helps you.....

 

[Anonymous]

Yes. This is good. However; where and why should someone use "index"? Is it the same as sorting or "order by". What is the advantage and usage of it?

Thanks

 

[Alexej Kubarev]

Search speeding up..

 

[Anonymous]

1. it's not the same as sorting or order by, but u can use index or primary key to do it
2. the advantage is :
- it will make u having a unique data in your database
- usually it used for beginner, coz for advance programmer they will make primary key based on the real condition for example :
beginner --> ID_Employee : 1-10 (using auto_number)
advance --> ID_Employee : KA-112, KD-2232 (based on real condition)

 

[Anonymous]

Indexes are used to identify records individually.
You can retrieve from the table the records you want using it.

$quer = "SELECT fname,lname FROM directory WHERE id=34";

It is also used when updating (edit,delete) the records. Horribly, without specifying the index of a record you are updating, the changes will reflect on all records.

$query = "UPDATE table SET deleted=1"; // absence of WHERE id=34

Lastly, read the manual. We all start not as gurus but as newbies reading manuals.

 

[Anonymous]

hey.... is anyone of you can help my problem i've described about making graphic 2d using data from mysql

 

[Alexej Kubarev]

please hold to the topic..
fftopic:

 

[Anonymous]

Hi All,

I've got a problem with an index..i already create an index for a column in 1 table.

+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| mobal | 1 | mobal_idx | 1 | DateSend | A | NULL | NULL | NULL | | BTREE | |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

this is the structure of mobal table:
+--------------+--------------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------+------+-----+---------------------+-------+
| RecordID | int(10) unsigned | | | 0 | |
| DateInsert | datetime | | | 0000-00-00 00:00:00 | |
| DateSend | datetime | | MUL | 0000-00-00 00:00:00 | |
| MobileNumber | varchar(11) | | | | |
| MONumber | varchar(20) | | | | |
| Message | varchar(225) | | | | |
| Status | enum('Status','Failure') | YES | | NULL | |
+--------------+--------------------------+------+-----+---------------------+-------+

the problem is...i dunno exactly how to query the index table. i tried browse in the internet on how to do that but still didnt get the solution...mayb becoz i'm not understand enough about indexing table.

this is my query : SELECT COUNT(*) FROM mobal WHERE DAYOFMONTH(DateSend)=DAYOFMONTH('2005-02-01 00:00:00') AND MONTH(DateSend)=MONTH('2005-02-01 00:00:00') AND YEAR(DateSend)=YEAR('2005-02-01 00:00:00');

for ur info, mobal table consists of 1 million data on that date ('2005-02-01 00:00:00). when i query this syntax, the process of retrieving the result very much slow....so...anyone pls help me...

thanks in advance

 

[Anonymous]

The problem is not the index. You can have a neatly indexed table but still have queries that take very long. In mi opinion the problem is that retrieving one million records takes a while so i think, you should try to revise what you want to do with one million records...

Regards.

 

[Anonymous]

Hi All...

Actually my application was designed to monitor SMS traffic so, there was a possibility that in 1 day, the traffic flows getting heavy, maybe minimum or more than 1 million. So, i had to increase the performance of my web by reducing the time taken to retrieve all the data for that particular month/day..should there is another way to solve my problem?

thanks in advance

 

[Anonymous]

Hi All,

I already changed my query to become as below:

SELECT COUNT(*) FROM mobal WHERE DateSend='2005-02-01';

but i still didnt know where n when to use the index column in the above query.

Can anyone help me !!!!...

 

[Anonymous]

Hi,

The query is ok, why would you want to use an index? the index retrieves an unique value, but if you want to retrieve all the info of a single day then you are fine.

Regards.