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;
}
...
  • @Strawberry why it's not a dba question by the way? It's about MySQL – NineCattoRules Oct 29 '16 at 10:08
  • @Strawberry my question is about performance indeed (I saw many others here)...I edited the question since I forgot the relation between tracks and users tables. AND uid=idu – NineCattoRules Oct 29 '16 at 10:15
  • @Strawberry Yes,tracks.id and users.idu – NineCattoRules Oct 29 '16 at 10:17
  • 1
    A covering index on time and the other column would probably perform better – Strawberry Oct 29 '16 at 10:20
  • If you're using commas to join tables, you still have some walking to do ;-) – Strawberry Oct 29 '16 at 12:15
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
  • thanks for the answer, but why it's wrong in my case something like this dba.stackexchange.com/a/77011/82129 ? Where he said "You might run these queries" – NineCattoRules Oct 29 '16 at 10:28
  • I don't understand what you're trying to say – Strawberry Oct 29 '16 at 10:29
  • I'm talking about this part: instead of this single query: SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql'; You might run these queries: SELECT * FROM tag WHERE tag = 'mysql'; SELECT * FROM tag_post WHERE tag_id=1234; SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904); – NineCattoRules Oct 29 '16 at 10:31
  • @SimonLeCat - ORDER BY RAND() is significant. You left it out of that comment. We need to know what the goal is. Also, please provide SHOW CREATE TABLE for the relevant tables. We need to know the indexes, etc. – Rick James Oct 29 '16 at 19:24
  • 2
    As for that link about changing to 3 queries... I know and respect Baron, Vadim, and Peter, but I disagree with this misleading suggestion. Most of the arguments in favor of the split up are so rare as to be not worth mentioning. Stick with a single query with JOINs, then let's work on improving it. – Rick James Oct 29 '16 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.