index กับ primary key ใน database ต่างกันยังไง

[qwerty]

ผมยังไม่ค่อยเข้าใจ key 2 ตัวนี้อ่ะครับว่า ต่างกันยังไง พอดีตอนนี้กำลังหัดเล่น mysql อยู่ ที่ผมเข้าใจคือ primary จะเป็นเอาไว้ทำให้ฟิลที่กำหนดนั้นมีค่าไม่ซ้ำกัน แล้วก็เอาไว้จอยกับฟิลอื่น ส่วน index นี่จะเอาไว้เวลาเรียงข้อมูลทำให้เร็วขึ้น แล้วทำไมเวลากำหนด index สามารถให้ใส่ได้หลายฟิล ไม่ใช่ 1 ฟิลด์ ต่อ 1 index เหรอ งงๆ อ่ะครับ ผู้รู้ช่วยอธิบายที

[s4721069]

การทำ Index มีประโยชน์ในการค้นหาข้อมูล Query ได้รวดเร็ว โดยการค้นหาจะเป็นการกระโดดไปยังตำแหน่งที่ต้องการได้เลย ทำให้ไม่จำเป็นที่จะต้องค้นหาหมดทุก Row ทำให้ Query ได้เร็วกว่าเดิมมาก

[qwerty]

ยกตัวอย่างของการสร้าง index ของตารางนึงครับ
1. Cardinality มันคืออะไรครับ ขนาดของข้อมูล ของ field ต่างๆ ที่นำไปเก็บ index เหรอครับ
2. year, month ,day จัด เป็น primary key ไปแล้ว ทำไมต้องไปจัด index แยกอีกอันนึงต่างหากครับ งงๆ

[danger21th]

index เขาจะสร้างตาม Query  นะครับ Query  แบบหนึงก็ใช้ index ละแบบ แล้ว ก็สร้างเยอะไม่ดี ทำให้การ update เข้าไปช้าลงด้วย
ต้องเลือกเฉพาะที่สำคัญ แล้วก็ทำเฉพาะ record ขนาดจำนวณมากด้วย

[TOOAds.com]

งงอยู่เหมือนกัน ที่ทำแนวผมนะ แต่ไม่รู้ถูกหรือป่าว เพราะ PRIMARY มันให้ทำแค่ฟิลด์เดียว ส่วน index ให้ทำได้หลายฟิลด์ เสริชหาไม่เจอที่ละเอียดเลย

PRIMARY = shop_id
INDEX = year
INDEX = month
INDEX = day
INDEX = product_id

พอจะนึกภาพออกนะ

ปล.จำได้ว่า wp ก็ทำแนวผมนะ ลองแกะของ wp ดู

[Robin]

Primary
-ค่าซ้ำไม่ได้
-มีได้แค่ 1 primary key ใน 1 table แต่สามารถ ประกอบกันจากหลายฟิลด์ได้(เช่น  shop_id,year ....เป็น pk)
-เมื่อสร้าง primary key ก็จะมีผลเป็น index โดยอัตโนมัต

Index
-สามารถมีได้หลาย index ใน 1 table
-มีผลต่อการเรียงข้อมูล การค้นหา
-มีหลายประเภท index ธรรมดา มีค่าซ้ำได้  unique index ซ้ำไม่ได้

ประมาณนี้

[tapanon]

PK = User Define index
Index = System Index     

[หนึ่งสุดหล่อ]

ยกตัวอย่างของการสร้าง index ของตารางนึงครับ
1. Cardinality มันคืออะไรครับ ขนาดของข้อมูล ของ field ต่างๆ ที่นำไปเก็บ index เหรอครับ
2. year, month ,day จัด เป็น primary key ไปแล้ว ทำไมต้องไปจัด index แยกอีกอันนึงต่างหากครับ งงๆ

Primary key ก็เป็น index ชนิดนึง ใน 1 ตาราง สามารถมี primary key ได้คีย์เดียว (หรือกลุ่มเดียว) และต้อง Unique (ไม่ซ้ำ) ด้วย

ส่วน index สามารถมีได้กี่อันก็ได้ และมีซ้ำก็ได้ ยิ่งมี index เยอะก็จะทำให้การค้นหาเร็วขึ้น แต่ก็จะทำให้เปลืองเนื้อที่มากขึ้นด้วย

Cardinality คือ จำนวนแถวของผลลัพธ์ของข้อมูลที่ได้ทำ index ไว้แล้ว ยิ่งค่านี่ต่ำกว่าจำนวนแถวทั้งหมดในตารางมาก จะยิ่งค้นหาเร็วขึ้นมากเท่านั้น

จากตารางตัวอย่างนี้จับ field ทุกอันที่คิดว่าควรจะเป็น index มาทำ primary key แต่ดันติดตรงที่มัดรวมทุกอันเป็น primary key หมดเลย เลยส่งผลให้ เวลาจะค้นหาทีก็ต้องไปค้นจากทุกแถวในตาราง (7แสนกว่าแถว) เทียบกับการแยกทำ index เป็น fieldๆไป เช่น shop_product_index_stat_idx2 เวลาค้นหาก็จะไปหาจากเพียง 291 แถว (แทนที่จะเป็น 7 แสน) ซึ่งเร็วกว่ากันเยอะมาก

ตารางนี้ถ้าให้ผมโม จะได้เป็น
Primary: shop_id  อันเดียวก็พอ
Index1: product_id
Index2: day/month/year   จะรวมหรือจะแยกก็ได้ แต่ส่วนตัวผมใช้ DATE ฟิลด์เดียวมีครบทั้งสามอย่าง แต่บางทีถ้าไม่ค่อยได้ใช้ก็ไม่ได้ทำ index พวกวันที่เลยครับ

[qwerty]

โอ้ว ขอบใจทุกคนมากครับ เข้าใจมากขึ้นเยอะเลย เสีบดายกด thank ให้มไ่ได้
เมื่อกี้ลอง ใช้ searh เปรียบเทียบดูระหว่าง

EXPLAIN SELECT *
FROM  `shop_product_stat`
WHERE YEAR =2010
ปรากฎว่า



มันไม่ใช้ key ค้นหาเลยครับ ทำให้วิ่งหาทุก row ของตาราง

แต่พอผมลองเติม ให้ where month ลงไปด้วย เช่น
EXPLAIN SELECT *
FROM  `shop_product_stat`
WHERE YEAR =2010
AND MONTH =8



มันกลับใช้ key
shop_product_stat_idx2
ที่ผมสร้างไว้ อยากรู้ว่าทำไมเหรอครับ

[qwerty]

ขุดๆ

[qwerty]

ไปนอนแล้ว

[qwerty]

ยังข้องใจอยู่นิดนึง

[ohmohm]

ขอเดานะครับ ตัว query optimizer น่าจะคำนวณแล้วว่า แค่ WHERE YEAR แล้วจะได้ rows ออกมาจำนวนมาก ฉะนั้นกวาดดูทั้งตารางไปเลย ( table scan )
แต่พอมี WHERE YEAR = .. AND MONTH = .. เป็นไปได้ว่า จะมีจำนวนข้อมูลออกมาไม่มาก จนการไปดูที่ index อาจจะเร็วกว่า ไม่รู้ว่ามันทำการ index scan แทนการทำ table scan ด้วยหรือเปล่า
อีกประการหนึ่งคือ column MONTH ขอเดาว่าใช้เก็บเดือน ทำให้ข้อมูลที่จะออกมาได้เป็นไปได้เพียงแค่ 1 ถึง 12 เท่านั้น การดูที่ index น่าจะได้ผลดีกว่า

อ่านประกอบ Index Selectivity
http://www.akadia.com/services/ora_index_selectivity.html

[choueu]

การทำ Index มีประโยชน์ในการค้นหาข้อมูล Query ได้รวดเร็ว โดยการค้นหาจะเป็นการกระโดดไปยังตำแหน่งที่ต้องการได้เลย ทำให้ไม่จำเป็นที่จะต้องค้นหาหมดทุก Row ทำให้ Query ได้เร็วกว่าเดิมมาก

+1 

[ohmohm]

ยังข้องใจอยู่นิดนึง

ดูค่า selectivity ซิ โดยการ
SELECT 1.0 * count( distinct day )/ count(*) FROM `shop_product_stat`
SELECT 1.0 * count( distinct month )/ count(*) FROM `shop_product_stat`
SELECT 1.0 * count( distinct year )/ count(*) FROM `shop_product_stat`