CODEWITHSUNDEEP
phpmysqlsql
DJSweetness
DJSweetness

Reputation: 153

PHP and SQL: code is really slow

$unique = array();
$sql = "SELECT ID, TitleName, ArtistDisplayName, Mix FROM values_to_insert as A
        WHERE A.ID = ";

//Get a single row from our data that needs to be inserted...
while($result = $conn->query(($sql. $count)))
{

    //Get the $data of the single row query for inserting.
    $data = mysqli_fetch_row($result);
    $count++;

    //SQL to get a match of the single row of $data we just fetched...
    $get_match = "SELECT TitleName_ti, Artist_ti, RemixName_ti from titles as B
                    Where B.TitleName_ti = '$data[1]'
                    and B.Artist_ti = '$data[2]'
                    and B.RemixName_ti = '$data[3]' 
                    LIMIT 1";

    //If this query returns a match, then push this data to our $unique value array.
    if(!$result = $conn->query($get_match))
    {
        //If this data has been pushed already, (since our data includes repeats), then don't 
        //put a repeat of the data into our unique array. Else, push the data.
        if(!in_array($unique, $data))
        {
            echo 'Pushed to array: ' . $data[0] . "---" . $data[1] . "</br>";
            array_push($unique, $data);
        }
        else
            echo'Nothing pushed... </br>';
    }
}

This has taken 5+ minutes and nothing has even printed to screen. I'm curious as to what is eating up so much time and possibly an alternative method or function for whatever it is taking all this time up. I guess some pointers in the right direction would be great.

This code basically gets all rows, one at a time, of table 'A'. Checks if there is a match in table 'B', and if there is, then I don't want that $data, but if there isn't, I then check whether or not the data itself is a repeat because my table 'A' has some repeat values.

Table A has 60,000 rows Table B has 200,000 rows

Upvotes: 1

Views: 83

Answers (1)

Kickstart
Kickstart

Reputation: 21523

Queries within queries are rarely a good idea

But there appear to be multiple issues with your script. It might be easier to just do the whole lot in SQL and push the results to the array each time. SQL can remove the duplicates:-

<?php 

$unique = array();
$sql = "SELECT DISTINCT A.ID, 
                A.TitleName, 
                A.ArtistDisplayName, 
                A.Mix 
        FROM values_to_insert as A
        LEFT OUTER JOIN titles as B
        ON B.TitleName_ti = A.ID
        and B.Artist_ti = A.TitleName
        and B.RemixName_ti = A.ArtistDisplayName
        WHERE B.TitleName_ti IS NULL
        ORDER BY a.ID";

if($result = $conn->query(($sql)))
{
    //Get the $data of the single row query for inserting.
    while($data = mysqli_fetch_row($result))
    {
        array_push($unique, $data);
    }
}

As to your original query.

You have a count (I presume it is initialised to 0, but if a character then that will do odd things), and get the records with that value. If the first id was 1,000,000,000 then you have done 1b queries before you ever find a record to process. You can just get all the rows in ID order anyway by removing the WHERE clause and ordering by ID.

You then just get a single record from a 2nd query where the details match, but only process them if no record is found. You do not use any of the values that are returned. You can do this by doing a LEFT OUTER JOIN to get matches, and checking that there was no match in the WHERE clause.

EDIT - as you have pointed out, the fields you appear to be using to match the records do not appear to logically match. I have used them as you did but I expect you really want to match B.TitleName_ti to A.TitleName, B.Artist_ti to A.ArtistDisplayName and B.RemixName_ti to A.Mix

Upvotes: 2

Related Questions