Do we use a large table or multiple small tables in MySQL?
Recently we had an interesting discussion regarding the redesigning of our database. When we design a database, it is quite important to use the suitable tables, correct datatypes and proper indexing. However, finding the best design is almost impossible due to the complexity of data that have to be dealt with day-today projects. My biggest question was whether should we use one large table or multiple small tables to store network data inside our database. In order to find the answer to my question, I did execution time comparison by making one large table and one small table and then joining them with gene information table. I used identical number of columns and indexing for both large and small tables. Following picture shows the basic design of the MyISAM tables for both network and gene information.
The small table (network_single)contains 1655022 records belongs to one experiment and the large table (network_many) contains 5763305 records belongs to four experiments. Then I used following command to extract the number of records for 468 random gene IDs.
SET @GENES=”Potra2c131s34676,…”
select count(gene_info.gene_i) from network_many left JOIN gene_info on gene_info.gene_i= network_many.gene_i1 where FIND_IN_SET(gene_info.gene_id,@GENES) and network_many.dataset=”aspwood” union select count(gene_info.gene_i) from network_many left JOIN gene_info on gene_info.gene_i= network_many.gene_i2 where FIND_IN_SET(gene_info.gene_id,@GENES) and network_many.dataset=”aspwood”;
I got a small execution time when I use small table and here is the results.
To fetch data from large table took 154 seconds
To fetch data from small table took 3 seconds
In conclusion using multiple small tables makes the system much faster.
