PHP SQL / multible select request / slow

Question

I have 2 requests on a SQL database. It takes really long to load the whole script / page. Is there a better (faster) way? My code looks like this

    $abfrage = "SELECT * FROM table_a WHERE state = '0' ORDER BY EDIT DESC";
        $stmt = $pdo->query($abfrage);

        foreach($stmt as $data) { ?>

$usern = $data['user_name'];
$stmt = $pdo->query("SELECT * FROM tabl_b WHERE user_name = '$usern' ORDER BY follower DESC Limit 1");
$stmt->execute();
$pl = $stmt->fetch();

<?php echo $data['ID'];?>
        <?php echo $data['ID'];?>

        <?php if ($data['company'] == '') { ?>
        <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['user_name']."</span>"; } else { echo $data['user_name'];}?></a>
        <?php } else {?>

        <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['company']."</span>"; } else { echo $data['company'];}?></a>

        <?php } ?>

        <?php echo $data['PL_read'];?>
        <?php echo $data['PL_total'];?>
        <?php echo $data['country'];?>
        <?php echo $data['EDIT'];?>
    <?php echo $pl['follower'];?>
        <?php } ?>

I have a table around th echo data. I would be great if someone can give me a tip.

is there too many object into your table?, i'm not really into php, but i had this problem in java and the only way to speed this kind of thing is paginating them. — Paulo Galdo Sandoval
– Paulo Galdo Sandoval, Commented Oct 12, 2016 at 14:24

codtex · Accepted Answer · 2016-10-12 13:40:02Z

2

First if tables have relation I suggest you to take the data in a single call with join.

SELECT * FROM table_a a
LEFT JOIN tabl_b b ON a.user_name = b.user_name
WHERE a.state = '0'

Second for speeding up the things you can put INDEX on the two fields that you are using in the WHERE clause - state, user_name

ALTER TABLE `table_a` ADD INDEX `state` (`state`)
ALTER TABLE `tabl_b` ADD INDEX `user_name` (`user_name`)

edited Oct 12, 2016 at 13:40

answered Oct 12, 2016 at 13:34

codtex

6,5882 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LJSven Over a year ago

Wow - it´s much faster. What happens, when i add INDEX to the table?

codtex Over a year ago

Yes it should, if there is no INDEX the search is performed against the whole table data. When you have INDEX the search is made only in the necessary fields dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

Gunaseelan · Accepted Answer · 2016-10-12 15:55:24Z

When it comes for loading time then we should only fetch those column from table which are required, So in first query we can fetch user_name, ID, company, PL_read, PL_total, country, EDIT

Since the query which are running inside loop will also take time, so to avoid this use below code

    $dataArray = array();
    $userName = NULL;
    $abfrage = "SELECT ID, user_name, company, PL_read, PL_total, country, EDIT FROM table_a WHERE state = '0' ORDER BY EDIT DESC";
    $stmt = $pdo->query($abfrage);

    foreach($stmt as $data) {
        //creating a string
        if (is_null($userName))
        {
            $userName = "'" . $data['user_name'] . "'";
        }
        else
        {
            $userName .= ",'" . $data['user_name'] . "'";
        }
        $dataArray[$userName] = $data;
    }

    $stmt = $pdo->query("SELECT follower, user_name FROM tabl_b WHERE      user_name IN ($userName) ORDER BY follower");

    foreach($stmt as $data) { 
        if(isset($dataArray[$userName]))
        {
            $dataArray[$userName]['follower'] = $data['follower'];
        }
        else
        {
            $dataArray[$userName]['follower'] = 'N/A';
        }
    }

    foreach($dataArray as $data) { 
            echo $data['ID'];?>
            <?php echo $data['ID'];?>

            <?php if ($data['company'] == '') { ?>
                <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['user_name']."</span>"; } else { echo $data['user_name'];}?></a>
            <?php } else {?>
                <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['company']."</span>"; } else { echo $data['company'];}?></a>

            <?php } ?>

            <?php echo $data['PL_read'];?>
            <?php echo $data['PL_total'];?>
            <?php echo $data['country'];?>
            <?php echo $data['EDIT'];?>
            <?php echo $pl['follower'];?>
 <?php } ?>

Collectives™ on Stack Overflow

PHP SQL / multible select request / slow

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related