I've got an RDF table in MySQL 5.1 with the MyISAM engine. Here's the table definition:
CREATE TABLE `node` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `graph` varchar(100) CHARACTER SET latin1 DEFAULT NULL, `subject` varchar(200) NOT NULL, `predicate` varchar(200) NOT NULL, `object` mediumtext NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `nodeindex` (`graph`(20),`subject`(100),`predicate`(100),`object`(1 00)), KEY `ix_node_subject` (`subject`), KEY `ix_node_graph` (`graph`), KEY `ix_node_object` (`object`(255)), KEY `ix_node_predicate` (`predicate`), KEY `node_po` (`predicate`,`object`(130)), KEY `node_so` (`subject`,`object`(130)), KEY `node_sp` (`subject`,`predicate`(130)), FULLTEXT KEY `node_search` (`object`) ) ENGINE=MyISAM AUTO_INCREMENT=468086207 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
User-generated queries on this table construct multiple LEFT JOINs and perform full-text searches, and these queries get stuck in the
Copying to tmp table state.
Now, Gods know I'd love advice on how to improve this, but my real question here is this: how big would the temporary table be? It would seem plausible that it needs to construct n temp tables for n joins, each of 80 million rows.
I guess I wasn't clear: I'm wondering how you can estimate (or even better, compute) how big the temp tables would get. This is more a question about the implementation of a left outer join than anything.
Let me try to restate the question. I've got the table above, with 80 million rows. (Actually 81 now.) I've got a transient query, generated by a tool, that filters these 81 million rows by building a bunch of
LEFT JOINs and then doing a full text search on the
object field. To do the text search, as I understand things, it creates a temporary table, and since we're hypothetically searching the whole table that will also have 81 million rows. So that's one.
Let's say the number of rows is r.
Now, consider a left join of the table on itself; this results in a new table with r rows. So we're got one temp table because it always builds a temp table for text searches. Does that left join now create a second temp table? Do successive left joins create successive copies of the temp table, to that there are r+1 tables created in total? Or does it "add" the new columns to the existing table?
If it's done "in place" that means the length of the row in the joined table increases by the length of one source row for each join, and thus the total storage needed is growing as O(n) for n joins. But if it constructs a table because of the text search, then a table containing (row,row), then a table containing (row,row,row) and so forth (and now you've got an earworm for the rest of the day) then the space required grows as O(n2).
In other words, I'm asking does the total size of the table grow as O(n) for n joins, or O(n2)?