I want to understand some special inner properties of mysql.
So, I have task:
I need to create large database (size of it would be about 50 millions of rows).
I have one map-table and 5 tables-satellites.
Map table: (here is 50 mlns)
first_table_id (1000 distinct values)
second_table_id (100 distinct values)
third_table_id (1.000.000 d.v)
fourth_table_id (300.00 d.v.)
fifth_table_id (1000 d.v.)
Stuctures of satellites:
first: id + many char(255) fields - the characteristics of this record type
second: id + blob (data)
third: id + char (255)
fourth: id + char(255)
(!) fifth: id + 13 set columns, each of them has 2-15 values.
So I have a lot of join queries such as joining map-table with satellites (especially third, fourth and fifth) and map-table - map-table joins (on first and second columns) - I need in that joins to have a table whith 2-3 fourth columns and 2-3 fifth columns.
I know exactly, that I wouldn’t have a join to have a table larger than 3 fourth and 3 thrid columns.
But also I have queries that uses just map-table whith fourth table, map-table with third table, with fifth table and all combinations (3,4,5,3-4,3-5,4-5,3-4-5)
Would it be better, if I alter map table to have 3 fourth and 3 fifth columns?
(I would have MYI and MYD files much more large, so maybe I would have problems with HDD I/O? Now map-table is about 2.5 Gb)
Should I join all set-columns in fifth table in one? - I would have one index for all columns, so maybe searching in one big set would be faster?
Set is just binary mask, so it is very small - maybe I should place one big set column in map-table? (if answer on the first question is “yes” then my answer is should I place 3 big set columns ) )
Would I have economy on join with fifth table using this trick?
if answer on the first question is “yes”: how do I need to create indexes to search records fast? Full combinations of probably search fields:
3 - 4
3 - 5
4 - 5
3 - 3_1
3 - 4_1
3 - 5_1
3 - 3_2
3 - 4_2
3 - 5_2
and so on - all a-b and a-b-c combinations.
In that case the best thing is B-tree of Indexes )))
So MySQL doesn’t allow them…
Thank you very much for your answers, it’s really very important to me. I’m happy that I have found this site because nobody in the internet knows answer, and documentation doesn’t have answers too on these questions…