CODEWITHSUNDEEP
phpmysql
Sam
Sam

Reputation: 333

MySQL Performance: 2 Tables or 1 Table with a LONGTEXT type

I need to store data in a table that looks like this:

$sql = "CREATE TABLE $table_name (
        id int unsigned not null auto_increment,
        type varchar(15) default '' not null,
        name varchar(40) default '' not null,
        folder varchar(25) default '' not null,
        data mediumtext default '' not null,
        shared_network bool,
        owner int unsigned,
        shared_label bool,
        creator int unsigned,
        thumbnail int unsigned,
        PRIMARY KEY (id)
        )$charset_collate;";

My first question is, because the Data value is going to be a large value, would it be more performant to separate this into two tables: 1 with ID and Data and 1 with everything else?

Second, if it IS more performant to use two tables, which of these would be more performant: Running two queries, one to find the IDs that I need and one to get the Data value for those IDs. Or, an outer join on the ID (I would need all the values).

Finally, if an outer join is the best way to go, is ID a FOREIGN KEY on the table with ID and Data or is it a foreign key on the table with everything else?

Thanks.

UPDATE:

An example query would be:

SELECT * from $table_name WHERE
type='something'
AND shared_network=TRUE
AND shared_label=TRUE

Upvotes: 2

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

For this query:

SELECT *
from $table_name
WHERE type ='something' AND shared_network = TRUE AND shared_label = TRUE;

You can create an index on table(type, shared_network, shared_label). The index will satisfy the where clause. The rest of the work is fetching the appropriate rows. There is no advantage to having two tables.

Upvotes: 1

Related Questions