realjumper Posted January 24, 2007 Share Posted January 24, 2007 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 fieldsThe service_permissions (s) table has 3326 entries with 39 fieldsThe 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 :) Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/ Share on other sites More sharing options...
trq Posted January 24, 2007 Share Posted January 24, 2007 Is it working? How long does it take to return? Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-167748 Share on other sites More sharing options...
realjumper Posted January 24, 2007 Author Share Posted January 24, 2007 Yes it works, and returns the correct data. It takes about 4 seconds to load the page. Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-167750 Share on other sites More sharing options...
realjumper Posted January 24, 2007 Author Share Posted January 24, 2007 *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 :-) Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168324 Share on other sites More sharing options...
dgiberson Posted January 24, 2007 Share Posted January 24, 2007 what kind of table type are the tables setup as? InnoDB, MyISAM? Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168329 Share on other sites More sharing options...
boo_lolly Posted January 24, 2007 Share Posted January 24, 2007 where's the u.column_name coming from? another query? Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168333 Share on other sites More sharing options...
realjumper Posted January 24, 2007 Author Share Posted January 24, 2007 dgiberson: The tables are setup as MyISAM Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168335 Share on other sites More sharing options...
dgiberson Posted January 24, 2007 Share Posted January 24, 2007 [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] Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168336 Share on other sites More sharing options...
dgiberson Posted January 24, 2007 Share Posted January 24, 2007 [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. Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168339 Share on other sites More sharing options...
craygo Posted January 24, 2007 Share Posted January 24, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168340 Share on other sites More sharing options...
realjumper Posted January 24, 2007 Author Share Posted January 24, 2007 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 :-) Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168356 Share on other sites More sharing options...
realjumper Posted January 24, 2007 Author Share Posted January 24, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/35451-is-this-query-okay/#findComment-168362 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.