Diperlukan saran struktur data MySQL

Saya memerlukan saran mengenai cara mengatur data saya untuk pencarian teks yang efektif dan cepat.

Latar belakang

Saya memiliki aplikasi (dalam PHP) di mana pengguna dapat mengatur artikel dan secara dinamis membuat formulir dan bidang untuk tujuan itu. Artinya, satu artikel misalnya dapat memiliki atribut Jenis, Merek, Warna dan artikel lain misalnya dapat memiliki atribut Jenis, Bahan, Warna, Konten. Pengguna pada dasarnya dapat membuat atribut sebanyak yang dia suka...

Lalu saya harus bisa mencari dan mengurutkan di antara atribut-atribut yang "tidak diketahui" ini. Saya juga harus dapat membaca kembali semua atribut jika pengguna ingin mengedit artikel.

Solusi saya

Ide pertama saya (dan sejauh ini satu-satunya ide) adalah menyandikan semua atribut ke dalam satu bidang TEXT dengan indeks FULLTEXT (harus MyISAM berfungsi), seperti:

__Type="3",__Brand="Nokia",__Color="6"
__Type="2",__Material="7",Color="2",Content="MP3 Player,2 Apples, 1 book: Larry King"

Atribut akan mendapatkan awalan dan/atau postfix agar tidak tertukar dengan nilai dalam atribut. Atau buat serial atribut dengan JSON.

Lalu saya bisa membuat kueri berdasarkan atribut yang dipilih seperti:

SELECT * FROM Articles a
WHERE Attribute LIKE '%__TYPE="2"%'
AND Attribute LIKE '%__Color="2"%'

Jika suatu atribut kosong, atribut tersebut tidak akan disertakan dan hal ini memungkinkan untuk menyertakan pencarian pada semua artikel yang memiliki kumpulan atribut tertentu, berapa pun nilainya.

Masalah

Soal atau tidak, yang saya khawatirkan adalah performa pencarian saat database dipenuhi ribuan artikel.

Masalah lainnya juga adalah mencari kata tertentu di dalam atribut tertentu, seperti:

Content="MP3 Player,2 Apel, 1 buku: Larry King"

Katakanlah saya hanya ingin mendapatkan baris di mana atribut Content memiliki frase "Larry King" di suatu tempat. Saya rasa saya tidak bisa melakukannya dalam pertanyaan SQL yang sama tanpa mendapatkan kecocokan pada semua baris yang memiliki "Larry King" di suatu tempat.

Saya terbuka untuk segala jenis saran/diskusi mengenai tabel, bidang, dan hubungan apa yang harus saya buat untuk mencapai tujuan yang dijelaskan.

Terima kasih.


mysql php sql full-text-search database-design
person Max Kielland    schedule 12.01.2011    source sumber
comment
Apakah type=3 selalu merek dan warna; tipe=2 selalu: bahan, warna, konten?   -  person    schedule 12.01.2011
comment
Akan selalu ada atribut akar, namun atribut dapat disusun sebagai pohon di banyak tingkatan, sehingga Tipe di sini mungkin berakhir dengan kumpulan/bentuk atribut yang berbeda.   -  person Max Kielland    schedule 12.01.2011

Jawaban (1)


arrow_upward
4
arrow_downward

Jika Anda akan sering mencari nilai atribut tertentu, mengapa tidak menjadikan atribut tersebut sebagai kolomnya sendiri dalam tabel? Atau jika ingin struktur yang lebih fleksibel, buatlah tabel kedua seperti:

CREATE TABLE attributes (
 my_id int unsigned not null default 0,
 attribute_key varchar(255) not null default '',
 attribute_value varchar(255) not null default '',
 KEY (my_id),
 KEY (attribute_key),
 KEY (attribute_value)
);

Dalam hal ini, bidang my_id adalah kunci utama tabel utama Anda. Jadi, daripada membuat serial string seperti:

__Type="2",__Material="7",Color="2",Content="MP3 Player,2 Apples, 1 book: Larry King"

Anda malah akan membuat beberapa baris seperti:

INSERT INTO attributes VALUES (1, 'Type', '2');
INSERT INTO attributes VALUES (1, 'Color', '2');
INSERT INTO attributes VALUES (1, 'Content', 'MP3 Player,2 Apples, 1 book: Larry King');

Dan kemudian Anda akan merumuskan permintaan pencarian Anda seperti:

SELECT * FROM mytable 
LEFT JOIN attributes ON mytable.my_id = attributes.my_id 
WHERE attributes.attribute_key = 'Type' AND attributes.attribute_value = '2';

Ini tidak benar-benar menyelesaikan masalah kedua dari pertanyaan Anda, tetapi kinerjanya jauh lebih baik daripada melakukan pencarian teks lengkap di ribuan baris. Anda tentu saja kemudian dapat menambahkan indeks FULLTEXT pada bidang attribute_value juga untuk menanyakan fragmen teks seperti contoh "Larry King" Anda.

person futureal    schedule 12.01.2011
comment
+1 Ide yang sangat bagus, saya perlu memberikan beberapa kesulitan untuk melihat apakah itu akan memenuhi semua kebutuhan saya. Saya masih terbuka untuk saran lebih lanjut... - person Max Kielland; 12.01.2011
comment
@futureal Namun atribut tabelnya akan sangat besar, sekitar 5 atau 8 kali lebih besar dari tabel artikel sebenarnya. Apakah pendekatan ini masih memenangkan kinerja dibandingkan pemikiran awal saya? - person Max Kielland; 12.01.2011
comment
Ya, sangat banyak. Memiliki banyak baris tidak menjadi masalah jika diindeks dengan benar. Jika Anda memiliki n baris di tabel utama dan menjalankan kueri pada atribut tertentu, kueri tersebut hanya akan memeriksa n baris (paling banyak). Itu hanya akan melihat subkumpulan data yang relevan dengan kueri. - person futureal; 12.01.2011
comment
Saya menemukan solusi Anda untuk menjadi pilihan terbaik di sini, Terima kasih! - person Max Kielland; 12.01.2011
comment
Tidak masalah -- ini sebenarnya adalah pola desain SQL yang cukup standar sehingga baik untuk diketahui dan dipraktikkan. Semoga beruntung! :) - person futureal; 12.01.2011
comment
@futureal Sebuah pemikiran... Sepertinya atributnya terutama terdiri dari dua jenis: Numerik saja dan Teks. Apakah saya akan mendapatkan keuntungan dengan memiliki dua tabel Atribut, satu untuk bilangan bulat (seperti warna atau jenis) di mana bilangan bulat tersebut adalah ID dalam tabel string. Dengan pendekatan ini saya bisa membuat gabungan untuk mendapatkan nilai tekstual pada pertanyaan yang sama saat menampilkan artikel. Saya kira itu hanya tergantung pada tabel apa yang harus dicari. Saya kira indeks numerik indeks tertentu lebih efektif. - person Max Kielland; 12.01.2011
comment
Sepertinya solusinya - namun ketika menarik data saya akan menggunakan GROUP_CONCAT - ini akan menggabungkan semua atribut dalam 1 bidang (sesuai keinginan Anda) dalam 1 catatan - person ; 12.01.2011
comment
@Joanna Saya telah mengatur tabel dengan cara yang berbeda dan menggunakan UNION ALL untuk mengumpulkan semua data. Ini berlanjut di stackoverflow.com/questions/4668747/ - person Max Kielland; 12.01.2011