jkkenzie Posted February 25, 2012 Share Posted February 25, 2012 hi! i dont know if the query below from mysql is slowing down the page or i have not written it properly: It is very slow, even when you limit the results to 100 . Thanks in advance.. SELECT `tbl_business`.`appformno`, " _ & "`tbl_business`.`pobox`, `tbl_business`.`plotno`, `tbl_business`.`postalcode`, " _ & "`tbl_business`.`zonecode`, `tbl_business`.`ne`, `tbl_activities`.`act_code`, " _ & "`tbl_activity_codes`.`approved_fee`, `tbl_payment_status`.`status`, " _ & "`tbl_business`.`company`, `tbl_activity_codes`.`description` " _ & "FROM (`citycouncil`.`tbl_activity_codes` `tbl_activity_codes` " _ & "LEFT JOIN (`citycouncil`.`tbl_business` `tbl_business` " _ & "LEFT JOIN `citycouncil`.`tbl_activities` `tbl_activities` " _ & "ON `tbl_business`.`appformno`=`tbl_activities`.`appformno`) " _ & "ON `tbl_activity_codes`.`act_code`=`tbl_activities`.`act_code`) " _ & "LEFT JOIN `citycouncil`.`tbl_payment_status` `tbl_payment_status` " _ & "ON `tbl_activities`.`id`=`tbl_payment_status`.`activity_id` " _ & "WHERE tbl_business.appformno LIKE '%" & strFormNo & "%' " _ & "AND tbl_business.bid LIKE '%" & strBizIdNo & "%'" _ & " AND `tbl_business`.`contperson` LIKE '%" & strContPerson & "%' " _ & "AND `tbl_business`.`zonecode` LIKE '%" & strZoneCode & "%' " _ & " AND `tbl_activities`.`act_code` LIKE '%" & strBizActCode & "%' " _ & "AND `tbl_business`.`roadstreet` LIKE '%" & strStreet & "%' " _ & " AND `tbl_business`.`company` LIKE '%" & strBizName & "%' " _ & "AND `tbl_business`.`building` LIKE '%" & strBuilding & "%' " _ & "ORDER BY `tbl_business`.`appformno` Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted February 25, 2012 Author Share Posted February 25, 2012 Sorry! guys pasted the code from a simple windows form i test my queries. But i guess its still MySql Query Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted February 25, 2012 Share Posted February 25, 2012 sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`pobox`, `tbl_business`.`plotno`, `tbl_business`.`postalcode`, " _ ' at line 1 Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted February 25, 2012 Author Share Posted February 25, 2012 Hi! thanks for your response. There is no error on the sql. Just test with LIMIT 1 and gave a result. The database has 12,000 records, when i dont limit it takes forever to load Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted February 25, 2012 Author Share Posted February 25, 2012 All am looking for here is how i cand re0rganise this sql so that i can load all those records within some seconds. Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted February 25, 2012 Share Posted February 25, 2012 if you want to speed it up use memcache for your queries. http://php.net/manual/en/book.memcache.php Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted February 25, 2012 Author Share Posted February 25, 2012 Is there another solution? this one will be a big project to me if i use memcache Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted February 25, 2012 Share Posted February 25, 2012 you could enable MYSQl's cache query. http://www.techiecorner.com/45/turn-on-mysql-query-cache-to-speed-up-mysql-query-performance/ Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted February 25, 2012 Author Share Posted February 25, 2012 ok. thank you. 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.