How to better perform a query split into subqueries in PHP

Question

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
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
@Strawberry that wasn't my point...also I don't see how using join could be better for performance — NineCattoRules, Oct 29 '16 at 13:47

score 0 · Accepted Answer · answered Oct 29 '16 at 10:25

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

answered Oct 29 '16 at 10:25

Strawberry

126
6

thanks for the answer, but why it's wrong in my case something like this http://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
@RickJames Thanks Rick, I read here around that in terms of performance, using or not JOIN is exactly the same. Is that correct? – NineCattoRules Oct 30 '16 at 15:30

How to better perform a query split into subqueries in PHP

1 Answers1