JSHINER Posted May 23, 2007 Share Posted May 23, 2007 $limitvalue = $page * $limit - ($limit); $query = "SELECT * FROM Feed_3PT_SF, View_Member, View_Office WHERE Feed_3PT_SF.TOWN_NUM = '$id' AND Feed_3PT_SF.LIST_AGENT = View_Member.MLS_ID AND Feed_3PT_SF.LIST_OFFICE = View_Office.MLS_Number ORDER BY LIST_PRICE ASC LIMIT $limitvalue, $limit"; $result = mysql_query($query) or die("Error: " . mysql_error()); Is there anyway I can clean up this query? It takes way too long for 100 or so results to display on the page. Quote Link to comment Share on other sites More sharing options...
taith Posted May 23, 2007 Share Posted May 23, 2007 no... length of the query doesnt cause the speed issue... but the amount of information that that query unloads... if its too long of a loading time you'd either need to a) suck it up and wait... or b) find another way of further sorting your table... Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 if instead of SELECT * I did SELECT name.FEED_3PT_SF - since there are maybe 50 fields in each, but I only need about 5 - would that speed it up ? Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 Sorry - SELECT name.FEED_3PT_SF AS name, etc.. Quote Link to comment Share on other sites More sharing options...
per1os Posted May 23, 2007 Share Posted May 23, 2007 Also you may want to look into the INDEX field, and index fields you are searching on. But yea defining the columns is alot quicker than using the *. Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 INDEX field? Quote Link to comment Share on other sites More sharing options...
eric1235711 Posted May 23, 2007 Share Posted May 23, 2007 $limitvalue = $page * $limit - ($limit); $query = "SELECT * FROM Feed_3PT_SF, View_Member, View_Office WHERE Feed_3PT_SF.TOWN_NUM = '$id' AND Feed_3PT_SF.LIST_AGENT = View_Member.MLS_ID AND Feed_3PT_SF.LIST_OFFICE = View_Office.MLS_Number ORDER BY LIST_PRICE ASC LIMIT $limitvalue, $limit"; $result = mysql_query($query) or die("Error: " . mysql_error()); Is there anyway I can clean up this query? It takes way too long for 100 or so results to display on the page. Ja See if all those fields in the WHERE statement are indexed. Indexing them will increase performance a lot. You know what are keys and index fields, right? Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 How would I go about indexing them ? Quote Link to comment Share on other sites More sharing options...
eric1235711 Posted May 23, 2007 Share Posted May 23, 2007 Are you using phpMyAdmin? Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 Yes I am Quote Link to comment Share on other sites More sharing options...
per1os Posted May 23, 2007 Share Posted May 23, 2007 www.google.com search for mysql index You will have to alter the table. Quote Link to comment Share on other sites More sharing options...
eric1235711 Posted May 23, 2007 Share Posted May 23, 2007 first go to the table structure page. in the left bottom corner of the page there's an "Indexes" table. Check what's there... Maybe you will find a "no indexes" message there. to define new indexes click on the yellow thunder icons corresponding to the fields you wanna index. That will probably sove your problem... after, look for some tut explaining about primary keys, keys, unique fields and indexes. Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 I'll try that. Is that the best way to speed up a query? Quote Link to comment Share on other sites More sharing options...
per1os Posted May 23, 2007 Share Posted May 23, 2007 As long as what you are listing as index should be. Just be fore-warned that when doing an update or insert statement, it could take a little longer due to the fact that it has to index the data. Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 Should I define the columns AND index those fields and the WHERE, or just index the fields in the WHERE statement? Quote Link to comment Share on other sites More sharing options...
Guardian-Mage Posted May 23, 2007 Share Posted May 23, 2007 If loading is an issue you could clean up the code that prints it, or limit the results loaded at one time. -Brandon Quote Link to comment Share on other sites More sharing options...
eric1235711 Posted May 24, 2007 Share Posted May 24, 2007 First index the id columns and you will notice a greater performance. Indexing speeds up seaching, so do it in the fields used in conditions. in the WHERE and in the JOIN statements. It would be great for you seraching in the google for indexing... as frost recommended. Quote Link to comment Share on other sites More sharing options...
JSHINER Posted May 24, 2007 Author Share Posted May 24, 2007 I will definately read up on indexing - it made it much faster. Just what I was looking for. Thanks! Quote Link to comment 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.