Mysql – How to better perform a query split into subqueries in PHP

MySQLPHP

In PHP I have made this query:

$getTrackID = $this->db->query("SELECT `id`,`uid` FROM `tracks`, `users` WHERE `time` > CURDATE() - interval 1 year AND `uid`=`idu` AND `public` > 0 ORDER BY RAND() LIMIT 10");
$TrackID = $getTrackID->fetch_assoc();
$id=$TrackID['id'];
$xid=$TrackID['uid'];

The problem is that it takes 1 sec to return the result. I saw this question and it seems better practice split big queries into subqueries.

Is this the case? To better perform should I need to use something like:

$getTrackTime = $this->db->query("SELECT `id`,`uid` FROM `tracks`, `users` WHERE `time` > CURDATE() - interval 1 year AND `uid`=`idu`");
// Declare the rows array
$rows = array();
while($row = $getTrackTime->fetch_assoc()) {
    // Store the result into the array
    $rowsTime[] = $row;
}

$getTrackPublic = $this->db->query(sprintf("SELECT `%s`,`%s` FROM `tracks`, `users` WHERE `public` > 0",$rowsTime['id'],$rowsTime['uid']));
$rows = array();
while($row = $getTrackPublic->fetch_assoc()) {
    $rowsPublic[] = $row;
}
...

Best Answer

Quickly get into the habit of writing your queries with some kind of formatting, proper joins, and with properly qualified column names. It makes it much easier to understand. E.g.:

SELECT t.id
     , t.uid
  FROM tracks t
  JOIN users u
    ON u.idu = t.uid
 WHERE t.time > CURDATE() - INTERVAL 1 YEAR
   AND u.public > 0 
 ORDER
    BY RAND() 
 LIMIT 10