Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Update

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

share|improve this question
    
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
show 7 more comments

1 Answer

Temp Table can be as big as you want. But benefit is it doesn't remain on that table for long time. The data inserted in tempdb will get removed when the sql server is restarted.

share|improve this answer
    
Right. But I want to compute how big it would be? –  Charlie Martin Nov 30 '13 at 14:45
    
That's really not the question. –  Charlie Martin Nov 30 '13 at 14:59
1  
MySQL doesn't have a "TempDB"`. –  a_horse_with_no_name Nov 30 '13 at 16:11
    
Where does the question say "tempDB"? MySQL creates a temporary table either when you ask for one, or when it needs to do things like full text searches. Google the MySQL "Copying to tmp table". –  Charlie Martin Nov 30 '13 at 17:05
    
@CharlieMartin: ypercube is right. This was a criticism of Shoonil's answer. –  a_horse_with_no_name Nov 30 '13 at 17:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.