oracle765 Posted May 28, 2014 Share Posted May 28, 2014 Hi all we have an ajax query which queries a MySQL database for cities around the world for our hotel search. We are running on a Linux web hosted server. The problem we are having is when you first visit out site and type in Brisbane or Sydney it takes around 15 seconds maybe to return. After that what ever city you query is lightening fast. I have had a few friends try this and when they first visit our site they experience the same then second time around it is fast. Could this be some sort of caching issue or setting somewhere that I am missing? I have attached a file so you can see what I mean Thanks in advance Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/ Share on other sites More sharing options...
QuickOldCar Posted May 29, 2014 Share Posted May 29, 2014 (edited) at http://compareandchoose.com.au/hotel the following files do not exist http://compareandchoose.com.au/mega_dropmenu/js/tshCommon.min.js http://compareandchoose.com.au/css/demo.css at http://hotel.compareandchoose.com.au/ they are not being called. Edited May 29, 2014 by QuickOldCar Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481330 Share on other sites More sharing options...
oracle765 Posted May 30, 2014 Author Share Posted May 30, 2014 yes, if you look at the url it is compareandchoose.com.au/newlook/hotel as the compareandchoose.com.au/hotel just uses hotels combined search widget with compareandchoose.com.au/newlook/hotel we are querying expedias api and are trying to get the drop down to display the search results of cities which are in a MySQL database. when a user first visitis our site to search on a city which is extracted from our db the first time round this is very slow, second time around it is very fast please advise thanks Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481354 Share on other sites More sharing options...
QuickOldCar Posted May 30, 2014 Share Posted May 30, 2014 It seemed fast for me when I tried it. You can try posting your code here, maybe is something that would stand out. Can make an index in mysql http://dev.mysql.com/doc/refman/5.7/en/create-index.html Run explain to see whats going on http://dev.mysql.com/doc/refman/5.7/en/explain.html Check the slow query log http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481379 Share on other sites More sharing options...
oracle765 Posted May 31, 2014 Author Share Posted May 31, 2014 I can try and do this tomorrow for you, I am not that advanced in MySQL but I will give it ago. But I would like to mention first that I do not think its the actual query as when I run the query in MySQL itself it returns in milliseconds. I think it is initially between the user first visiting our website, I think because they first visit and try to search for a city we then connect to the db and issue the ajax query so I think it could be something where we initiate the connection initially what is causing they delay. Could I be correct? thanks Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481477 Share on other sites More sharing options...
mac_gyver Posted May 31, 2014 Share Posted May 31, 2014 (edited) without any specific information to go by (i.e. profiling your site and profiling your code to find out exactly where the time is spent and how it changes between the first visit and follow on visits), this is just a general guess based on the symptom. your web server and/or database server may be either allowing the disk drives to come to a stop (in general, disk drives on servers run continuously) or they are not configured with a significantly long enough (in time) disk caching (query caching for the database server) so that the initial page request is always reading from the disk instead of from the disk cache/query cache. i did check your page - compareandchoose.com.au/newlook/hotel at pingdom.com - http://tools.pingdom.com/fpt/#!/xwShk/compareandchoose.com.au/newlook/hotel and the only serious thing that jumps out is your bg2.png image is 1.5mb, it's also apparently loaded via javascrpt and so doesn't even get requested until after the javascript library has been loaded and runs. this may not be relevant to your problem, but one of the times that i can recall a php application taking a huge amount of time on the first page load after a period of inactivity was for a shopping cart script that was built using a 'read everything regardless of it was even used' plugin method that literally included every possible/optional file in a collection of folders and was reading several hundred files (200+) every time a page was requested and when the content of these files was not in the disk cache, they had to be read from the disk. Edited May 31, 2014 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481497 Share on other sites More sharing options...
oracle765 Posted June 1, 2014 Author Share Posted June 1, 2014 ok thanks I will look into the png problem. in the meantime I have collected all the files that I think may be getting called which includes the actual ajax query and also a screen shot of the database tables because this was written by an offshore company so I am actually not sure what is exactly going on or exactly where the problem lies . I hope this helps & thanks again for you taking the time to assist me it is very much appreciated config.txt hotel form check.txt hotel search ajax.txt hotel.txt Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481516 Share on other sites More sharing options...
mac_gyver Posted June 1, 2014 Share Posted June 1, 2014 i have reviewed your recent threads, this thread again, the code you posted above, and tried the page. it would seem this is the third thread since the start of this year where you have mentioned slow response times. do pages on your site that also depend on the database work as expected? is this only when searching via ajax? do you have more than one database server and this only occurs for one specific server? the only things i have to recommend at this time are - 1) add a page generation time calculation to the hotel search ajax.php page, using microtime(true), and log it to a file, along with the date/time, ip address, and the search value. you can then look at this log of information to both see if the problem is in getting the data from the database server or is elsewhere and if the problem only occurs after a period of inactivity. 2) the code has NO security against sql injection. the search string needs to be escaped before putting it into the sql query statement. also, since the purpose of hotel search ajax.php is to only service the ajax request, the database connection code should be inside the if($_POST) { ... } logic. Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481538 Share on other sites More sharing options...
oracle765 Posted June 1, 2014 Author Share Posted June 1, 2014 (edited) hi mac_gyver thanks for that, I have put the connection info within the If statement to only serve the purpose of the Ajax and by the looks of things that seems to have done the trick, I asked a friend to try it to and he said it was instant, many thanks for that, I will double check this in the morning after a period of time to make sure its stable but I can tell you I tried on ipad and mobile phone and it was quick so looks like it was the issue anyway you mentioned in your reply that there is no security against sql injection. the search string needs to be escaped before putting it into the sql query statement. can you explain this in liament terms please as I have no idea how to do that thanks again alan Edited June 1, 2014 by oracle765 Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481540 Share on other sites More sharing options...
oracle765 Posted June 1, 2014 Author Share Posted June 1, 2014 hi again mca_gyver I gave it an hour then logged on to my laptop again and the search results dropdown was really slow, about 30 seconds so it didn't fix the problem after a period of time Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481542 Share on other sites More sharing options...
mac_gyver Posted June 1, 2014 Share Posted June 1, 2014 (edited) the suggestions to put the connection lines inside the logic was just to prevent unnecessary database connections when things like search engines or bot scripts request the page using a request that's not from your ajax based search page. the delay symptom is consistent with the database server's disks not being configured to run continuously and you must wait for the disk to start and come up to speed before it can be accessed, though the delay would be more likely less than 10 seconds. the database server may be experiencing a more serious problem. in any case, you must find the actual cause of the problem before you can do anything to fix it. as to escaping string data, all string data being put into a sql query needs to be escaped to prevent any sql special characters from breaking the sql syntax, which also allows hackers to inject their own sql into your sql statement. for the php mysql_ functions, you would use mysql_real_escape_string() (and if you happen to be using an older version of php, you need to test if php is trying to escape the data and actually undo what php is doing before properly escaping the data yourself.) however, the mysql_ functions are now depreciated and you should be using msyqli or PDO functions (which will let you use prepared query statements that also prevent sql injection.) Edited June 1, 2014 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/288833-ajax-query-taking-a-while-to-return/#findComment-1481553 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.