Is this query okay?

[realjumper]

Here is the query that seems to run quite slow...is something wrong with it?

[code]
$result = mysql_query("SELECT u.sid, u.student_staff, u.status, s.supa_admin, t.full_name FROM true_name t INNER JOIN service_permissions s ON t.sid = s.sid INNER JOIN users u ON s.sid = u.sid
WHERE s.$permission_cat = 'No' AND u.student_staff = 'staff' AND u.status = 'Active'
ORDER BY sid LIMIT 180,20;")
or die(mysql_error());
[/code]

$permission_cat is passed via the url.
The users (u) table has 3395 entries with 7 fields
The service_permissions (s) table has 3326 entries with 39 fields
The true_name (t) table has 3326 entris with 7 fields.

The result from this query will never return more than 155 results, as there are 155 staff members, the rest are students.

Can anyone see anything wrong?

Thanks  :)

[trq]

Is it working? How long does it take to return?

[realjumper]

Yes it works, and returns the correct data. It takes about 4 seconds to load the page.

[realjumper]

*bumping* this up because I'm sure this query is causing the slow page loading....if anyone could take a peek at the code please? Thanks :-)

[dgiberson]

what kind of table type are the tables setup as? InnoDB, MyISAM?

[boo_lolly]

where's the u.column_name coming from? another query?

[realjumper]

dgiberson: The tables are setup as MyISAM

[dgiberson]

[quote author=boo_lolly link=topic=123753.msg512552#msg512552 date=1169667162]
where's the u.column_name coming from? another query? << no he defined an alias for the table users u
[/quote]

[dgiberson]

[quote author=realjumper link=topic=123753.msg512554#msg512554 date=1169667329]
dgiberson: The tables are setup as MyISAM
[/quote]


I take it this page is hit a lot? One possible explanation for the slow page load could be because if this db is taking in a lot of hits, MyISAM is a table locking format meaning that it will not process a request until the previous requests touching that table are complete (even if its only processing 1 record). I would suggest switching over to a row locking table type ie. InnoDB which could speed this up a bit. Secondarily, I would also check to see what you have the query cache set to on the server if it's setup at all. If you crank the cache up this should also assist in speeding up the returns.

[craygo]

Can try this
[code]
<?php
$sql = "SELECT u.sid, u.student_staff, u.status, s.supa_admin, t.full_name
          FROM (true_name t LEFT JOIN service_permissions s ON t.sid = s.sid)
          LEFT JOIN users u ON s.sid = u.sid
          WHERE s.$permission_cat = 'No' AND u.student_staff = 'staff' AND u.status = 'Active'
          ORDER BY sid LIMIT 180,20;"
$result = mysql_query($sql) or die(mysql_error());
?>[/code]

Ray

[realjumper]

dgiberson: Actually, this page is hit only by me at this stage!!....which is why I have the concerns that I have....I shudder to think what might happen when 600 or so students hit it at the same time!!. I didn't realize that MyISAM worked they way you have explained it, thank you for that. I'll get the network admin to have a look at the query cache and see what we can discover. Many thanks :-)

[realjumper]

craygo: Thank you for this query. I remember that you patiently explained to me last year about joining 3 tables and I must admit to not doing too much research on LEFT JOINs as last time the INNER JOIN worked well. I'll do some research while I sip my coffee and see what I can learn. I think with your solution and dgiberson's help, I might make some progress today :-) Thanks heaps