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. Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/ 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... Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260167 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 ? Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260173 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.. Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260174 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 *. Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260176 Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 INDEX field? Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260191 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? Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260195 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 ? Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260198 Share on other sites More sharing options...
eric1235711 Posted May 23, 2007 Share Posted May 23, 2007 Are you using phpMyAdmin? Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260200 Share on other sites More sharing options...
JSHINER Posted May 23, 2007 Author Share Posted May 23, 2007 Yes I am Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260202 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. Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260203 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. Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260206 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? Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260262 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. Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260271 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? Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260365 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 Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260367 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. Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260713 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! Link to comment https://forums.phpfreaks.com/topic/52688-solved-cleaning-up-a-query/#findComment-260882 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.