สำหรับเรื่องการทำ index mysql เขียนไว้ดี เลยเอามาแบ่งปัน ครับ

[golfreeze]

เอาคร่าวๆละกันนะครับ

การเลือกฟิลด์ที่จะสร้าง index ให้เลือกฟิลด์ที่มีข้อมูลอยู่ในทุก where criteria
เช่น select * from abc where name='xx' and group='yy'
index ที่ต้องสร้างคือ name กับ group
แต่ถ้า group มีข้อมูลให้เลือกแค่ สัตว์บก, สัตว์น้ำ, สัตว์ปีก 3 อย่าง
อย่างนี้ไม่จำเป็นต้องสร้าง index ให้ group เพราะไม่ช่วยเพิ่มประสิทธิภาพเท่าใหร่
index ควรสร้างเฉพาะฟิลด์ที่มีข้อมูลแตกต่างกันมากพอควรจะมีประโยชน์กว่า
ถ้ามี select statment อื่นที่มี where criteria ของฟิลด์อื่น ก็ควรสร้าง index ให้ฟิลด์นั้นด้วย

index หลายๆตัวจะเปลืองพื้นที่เก็บ index และมีผลกับการ insert, update ช้าลงบ้าง แต่จะทำให้ select ไวขึ้น

primary key ควรเลือกตัวที่มีขนาดเล็กที่สุด และต้องมีค่าในแต่ละ row ไม่ซ้ำกันเลย

โดยคุณ Extra Cash

[golfreeze]

เสริมอีกหน่อยแล้วกันครับ primary key ควรจะ้เป็น field ที่เก็บค่าที่เป็นเอกลักษณ์ของ row ซึ่งแต่ละ row จะต้องไม่เหมือนกัน
ซึ่งเป็นตัวเลขจะดีที่สุด

--- ยกตัวอย่างเช่น ---
วิธีการเลือก primary
เช่น ประเทศไทย มีคนชื่อ-นามสกุล เหมือนกันหลายคน เราจะรู้ได้ว่า ใครเป็นใคร ด้วย หมายเลขประจำตัวประชาชนซึ่งไม่ซ้ำกันแน่นอน
primary คือหมายเลขประจำตัวประชาชน
--- จบตัวอย่าง ---

ฉะนั้นเวลาสร้างฐานข้อมูลควรจะมีฟิลด์ ที่เป็น identifier ด้วยสัก 1 ตัว โดยเปรียบจากตัวอย่างด้านบนแล้ว ฟิลด์นี้ก็เหมือนหมายเลขบัตรประชาชน
ฉะนั้นเวลาเราสร้างฐานข้อมูลก็ควรจะมีฟิลด์ id สักหน่อย เพื่ออ้างอิงข้อมูลได้ กรณีที่มี 2 ข้อมูลขึ้นไปที่มีค่าเหมือนกัน เราจะใช้ id เพื่ออ้างอิงได้ว่าเราจะเลือกอันไหน
คุณอาจเกิดคำถามว่าแล้วค่าของ field id นี้จะเอามาจากไหน อันนี้ส่วนใหญ่ที่นิยมจะใช้เป็น A_I (Auto Increment) ซึ่งค่าของฟิลด์นั้น mysql จะกำหนดเองอัตโนมัติ
(การจะกำหนดให้ใช้ A_I ต้องเลือกใน option ของ field ตอนที่จะสร้าง field ครับ)

ส่วนเรื่อง index ถ้าจะเปรียบง่ายๆก็เหมือน mysql เป็นหนังสือ 1 เล่ม หากเราผู้เขียน ใส่สารบัญเยอะเกินไป จะเกิดข้อเสียเช่น (เปรียบเป็นสารบัญ เพราะบางคนไม่รู้จักว่าดัชนีแปลว่าอะไร)
ประเภทว่า มีสารบัญบอกว่าหน้านี้บรรทัดที่เท่านี้เขียนว่าอะไร ลองคิดดูว่า สารบัญมันจะมากกว่า ข้อมูลเสียอีก
mysql ก็เช่นเดียวกัน หากมี index=สารบัญ มากเกินไป ก็จะทำให้ผู้อ่านหนังสือเล่มนั้นหาข้อมูลที่ต้องการอ่านได้ช้าลง

ฉะนั้น index (ในเชิงทั่วไป)เราควรจะเลือกเฉพาะ field ที่เป็นเงื่อนไขของการค้นหาเท่านั้นจะดีที่สุด

โดยคุณ mrtomson

[golfreeze]

เรื่อง Index ต้องถือว่าเป็น ศาสตร์ และ ศิลป์ ลึกลับ หาอ่านยากมากๆ 5555+

Index เอาเป็นว่าของ MySQL ก่อนละกัน

ถือเป็นดัชนีของข้อมูล โดยการจัดเก็บ Index นั้นจะถูกเรียงข้อมูลไว้อยู่แล้ว ดังนั้นเวลาค้นหาจาก Index มันจะมีกระบวนการค้นหาตาม ดัชนี โดยที่ไม่ต้องไปไล่ข้อมูลทั้งหมด สมมุติว่าเรามี Index 1-100 ถ้าเราต้องการหาข้อมูล ที่ 88 กระบวนการจะไม่ได้เริ่มค้นหาตั้งแต่ 1 แต่จะจิ้มเข้าไปที่ 88 เลยและจะหยุดทำงานทันทีที่เจอ

ข้อเสียของ Index มีแค่ สิ้นเปลืองเนื้อที่ กับ ทำให้ Operation Insert, Update, Delete ทำงานช้าลง ซึ่งก็ไมีต้อง ซีเรียส เพราะพวกนี้มีแค่ 10% ในเว็บ ธรรมดา (แต่ว่าถ้าเป็น FB หรือ TW มันน่าจะ 50:50 ได้เลย -*-)

แล้วก็ Index มีประเภทที่แตกต่างกัน 3-4 ชนิด ต้องเลือกใช้งานให้ถูก แล้วก็ยังทีส่วนที่เป็นข้อยกเว้นอีกหลายๆอย่างเช่น

1. การสร้าง Index ที่มีข้อมูลซ้ำกันเกิน 50% เช่น Yes, No แบบนี้ Index จะไม่ทำงาน
2. แล้วก็การใช้ condition != < > พวกนี้ Index ไม่ทำงาน
3. แล้วก็การสร้าง index แบบ multi-columns อาทิเช่น (id, created_at, status)แบบนี้ถ้าเรา where ด้วย 3 ตัวพร้อมเพีรงกันจะเร็วมาก แต่ว่าต้องทำจาก ซ้ายไป ขวา ยังไง?
id อย่างเดียว ทำงาน
id created ทำงาน
created ไม่ทำงาน
created status ไม่ทำงาน

มันจะต้องจาก ซ้าย ไป ขวา เท่านั้น ไม่ก็ต้องสร้าง Index มาอีกชุดนึง

แล้วก็มี Index อีกประเภท ที่ต้องไปหาอ่านเพิ่มเติม เพรามัน มี condition ที่ต่่างไปอีก คือแบบ Full text

ลองหาอ่านใน web ของ mysql ดูครับ index ดีๆ นี่ลด resources ไปได้อย่าง มหาศาล จากล้าน เหลือ 1 เลยสู้ๆ

โดยคุณ Tee++;

[golfreeze]

B – You WILL be using the employee in where clauses like this:

select * from employees where name ='smith';

C – You WILL be generating reports, which will probably be alphabetic, like this:

select * from employees order by name asc;

So in this simple example it's easy to see when it would be important to use indexes.

So, you could do it like this:

create index name_index on employees (name);

You might be working on a more complex database, so it's good to remember a few simple rules.

- Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated.

- Indexes speed up where clauses and order by.

Remember to think about HOW your data is going to be used when building your tables.

There are a few other things to remember. If your table is very small, i.e., only a few employees, it's worse to use an index than to leave it out and just let it do a table scan. Indexes really only come in handy with tables that have a lot of rows.

So, if Joe’s Pet Shop was using this database, they would probably be able to leave the index off the "name" column.

[golfreeze]

http://dev.mysql.com/doc/innodb/1.1/en/innodb-create-index-examples.html
http://dev.mysql.com/doc/refman/5.0/en/create-index.html

[golfreeze]

CREATE INDEX B ON T1 (B);
CREATE UNIQUE INDEX C ON T1 (C);

Dropping InnoDB secondary indexes also does not require any copying of table data. You can equally quickly drop multiple indexes with a single ALTER TABLE statement or multiple DROP INDEX statements:

ALTER TABLE T1 DROP INDEX B, DROP INDEX C;

or:

DROP INDEX B ON T1;
DROP INDEX C ON T1;

[golfreeze]

CREATE INDEX part_of_name ON customer (name(10));

[golfreeze]

#การเรียกโชว์ index จาก ตาราง ใน mysql
SHOW INDEX FROM [tablename]

[golfreeze]

#!/bin/bash
 
if [ $# -lt 2 ]; then
        echo "You must specify database host"
        echo "Eg. script.sh MY_DATABSE 192.168.10.1"
        exit
fi
 
db="$1"
host="$2"
user="root"
 
stty -echo
read -p "Enter MySQL's Admin password: " password
stty echo
 
mysql -u $user --password=$password -h $host --batch --skip-column-names $db -e "SHOW TABLES" |
while read table; do
        echo $table &&
        time mysql -u $user --password=$password -h $host $db -e "ALTER TABLE $table ENGINE=INNODB"
done

[golfreeze]

เราสามารถสร้าง index สำหรับหลายๆ field (group_id,user,password,type_id)
ใน table=tbuser ได้ดังนี้ครับ ในส่วนของ innodb -> BTREE
mysql>create index id_name_master1 on tbuser(group_id,user,password,type_id) USING BTREE;