StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCombine many MySQL queries with logic into data file
text
Body
copied!<p>Background:</p> <p>I am parsing a 330 meg xml file into a DB (netflix catalog) using PHP script from the console.</p> <p>I can successfully add about 1,500 titles every 3 seconds <strong>until</strong> i addd the logic to add actors, genre and formats. These are separate tables linked by an associative table.</p> <p><strong>right now I have to run many, many queries for each title</strong>, in this order ( i truncate all tables first, to eliminate old titles, genres, etc)</p> <ol> <li>add new title to 'titles' and capture insert id </li> <li>check actor table for exising actor</li> <li>if present, get id, if not insert actor and get insert id</li> <li>insert title id and actor id into associative table</li> </ol> <p>(steps 2-4 are repeated for genres too)</p> <p>This drops my speed don to about 10 per 3 seconds. which would take eternitty to add the ~250,00 titles.</p> <p><strong>so how would I combine the 4 queries into a single query, without adding duplicate actors or genres</strong></p> <p>My goal is to just write all queries into a data file, and do a bulk insert.</p> <p>I started by writing all associative queries into a data file, but it didn't do much for performance.</p> <hr> <p>I start by inserting th etitle, and saving ID</p> <pre><code>function insertTitle($nfid, $title, $year){ $query="INSERT INTO ".$this->titles_table." (nf_id, title, year ) VALUES ('$nfid','$title','$year')"; mysql_query($query); $this->updatedTitleCount++; return mysql_insert_id(); } </code></pre> <p>that is then used in conjunction with each actor's name to create the association</p> <pre><code>function linkActor($value, $title_id){ //check if we already know value $query="SELECT * FROM ".$this->persons_table." WHERE person = '$value' LIMIT 0,1"; //echo "<br>".$query."<br>"; $result=mysql_query($query); if($result && mysql_num_rows($result) != 0){ while ($row = mysql_fetch_assoc($result)) { $value_id=$row['id']; } }else{ //no value known, add to persons table $query="INSERT INTO ".$this->persons_table." (person) VALUES ('$value')"; mysql_query($query); $value_id=mysql_insert_id(); } //echo "linking title:".$title_id." with rel:".$value_id; $query = " INSERT INTO ".$this->title_persons_table." (title_id,person_id) VALUE ('$title_id','$value_id');"; //mysql_query($query); //write query to data file to be read in bulk style fwrite($this->fh, $query); } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload