I've got an RDF table in MySQL 5.1 with the MyISAM engine. Here's the table definition:

  `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
  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`)
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.

More Update

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)?

  • Can you show us an example query? – ypercubeᵀᴹ Nov 30 '13 at 14:40
  • The behavior I'm observing in big queries is that the query hangs up everything in Copying to tmp table state. If I'm understanding the documentation correctly, this means it would be an in-memory table, as if it were going to disk the state should be Copying to tmp table on disk. The engine is indeed MyISAM. – Charlie Martin Nov 30 '13 at 14:57
  • Dude. I can tell it's I/O bound. O(n) or O(n^2)? – Charlie Martin Nov 30 '13 at 15:30
  • Raymond, get a grip. This isn't a question about a query. This is a question about how left joins are implemented. – Charlie Martin Nov 30 '13 at 15:43
  • 1
    but it depends on the query (are there derived tables or not - only left joins or inner joins as well - how the full search is used) and whether some (and which) of the indexes can be used. Table scans vs. index scans vs. index seeks have very different performance. (I do confess that I can't really answer the specific questions about how exactly temp tables are used in the execution.) – ypercubeᵀᴹ Nov 30 '13 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.