0

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;
}
...
marc_s
  • 9,052
  • 6
  • 46
  • 52
NineCattoRules
  • 173
  • 1
  • 4
  • 12

1 Answers1

0

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
Strawberry
  • 126
  • 6