Tune the Geolocation Mysql Query

Hello, Thanks for the invitation. "We are using geolocation on our website - we are using IP of the visitor to locate the timezone of the visitor and then we display content based on the timezone. We are using a geolocation mysql table with 3M rows for this". Is it a set of ip address ranges like [login to view URL] ip_start, ip_end 8.8.8.0,8.8.8.255,NA,US,California,"Santa Clara County","Mountain View",94043,37.4229,-122.085,5375480,-7,America/Los_Angeles,USCA0746 "He added the table and used a basic query." Did he create indexes? Caching? Did he use INET_ATON in the query? [login to view URL] At least I would convert ip_start and ip_end to integer and add indexes. Thanks

Hi there, I have read initial requirements & as per my understanding, you want to customize query which is using for geolocation data fetching. Here are my questions: - Can you please provide URL of your website and mention page on which you are feeling issue with this query? - Can you please provide access of your script to verify issue with query? I can then suggest and explain what can be done to fix this low speed issue. ++++++++++++ You may ignore the bid & once you answer above questions, I can then estimate it precisely. Look forward to hearing from you. Regards, YK LEADconcept

Hello There, I have gone through the work details you have posted and I see you are looking for a solution to accurately locate the user loading the website and show the content based on their location in bulk. Surely I can help you in fixing this problem. The approach your old developer had used is not what you should use for such usage. Any server you use or any optimised code you use won't perform as you want. Because working through 2 million rows, fastest query will also cause server slow down or website slowness if not server down. The best approach is to use online available resources via API that will only send API request once per user per website load reducing the server load to minimum. I have worked with such problems in Geolocation and I have reference to websites which provide an accurate location and usages what you are looking for in a fraction of cost per year ($200), But this will be the permanent and proper solution to your problems. Nothing else can work Let me know and we can discuss more in detail over chat. Thanks

Greetings, Hi, I am Senior Full Stack website developer having 14 years of experience in website development and designing. I have extensive knowledge of Core PHP and it's frameworks (Symfony, Laravel, Zend, CI, Cake), JS and it's Frameworks (React, Angular, Express, Backbone, Express, Node, Moment, Vue, Typescript), Ajax, HTML5, CSS3, jQuery, MySQL, and Bootstrap. I am very much interested in your project and want to get it started right away. I assure you that you will experience a WOW factor in my work. Awaiting your response, Regards, DA

Hi there, I have checked your project brief very carefully and can help you on this. So far I understand that from IP you find user GEO location from database. Instead of a MySQL database there are 2 options 1. Using 3rd party API for GEO 2. Use flat database ( .dat ) instead of MySQL. I have several years of experience designing and developing website and already custom web applications. Look forward to hear from you & start further discussion through project message board. Regards Khorshed RS Software

Hi There, Thank you for the opportunity You are using geolocation on your website which works fine when you receive maybe up to 100 visitors per second, but when that goes up to say 200 the server cannot process the query fast enough and the whole server stops. Old processes are backing up and new ones are coming in faster than old ones can be processed. Since he has no experience on how to better tune this query to be faster, we need a geolocation expert that can check it out and see what can be done. Please share the site URL so we can review the current scenario Looking forward to discussing in detail Thanks Mirth

Hello, We are always pleased to take part in the development of living products, which are created as an answer to real user needs. Looks like you have already went through a set of paths and processes including concept creation and product design. The programmatic implementation has also already been created and now it's time for a new product development stage. We would be able to assist you by investigating the current state of the product to estimate the possibilities of it's further development. Our team consists out of expert level software engineers, what enables us to research the project opportunities of your product to evolve in the directions required by your business needs. This research will allow us to inform you about engineering and business-related specifics of the product development and to ensure optimization of evolutionary investments. Best Regards, Andrei Costenco Team Lead, Business Analyst, Senior Engineer Dev Experts Team

Hello, My name is Sergey. I am Freelancer from Saint Petersburg, Russia. I can help you to speed up the Geolocation MySQL Query. I need access to your code and to MySQL database. I hope to finish in 2-3 working hours. This is list of similar projects, successfully completed by me: 1. "Improvise internal search speed" http://www.freelancer.com/projects/takereal_9800046.html 2. "MySQL Database Optimization" http://www.freelancer.com/projects/takereal_760804.html 3. "MySQL Database Tuning" http://www.freelancer.com/projects/takereal_860810.html 4. "Analysis of reasons for slow-running SQL queries" http://www.freelancer.com/projects/takereal_613446.html 5. "mysql optimization" http://www.freelancer.com/projects/takereal_541899.html I can communicate via Freelancer.com. -- Sergey aka Takereal

Hello, Please let me know 1. Did you do mySQL profiling? 2. Did he apply indexing 3. What is query time for geo location? And if possible, can you provide read only access to the database. Only and Only after that, we should be able to help you. Please get in touch. ~-~-~-~-~-~- About us ~-~-~-~-~-~- I am Zeeshan, and for most part of last 12 years working over C++(Windows and Mac desktop), Node.JS/PHP/Python (Web and API), Swift/ObjC(iOS) and Java/Kotlin(Android). We have worked over 200+ projects in last 3-4 years alone with the help of a small and very trustable team that works under the same roof. We have best reviews of any freelancer here , 5 / 5 for all the reviews. www.freelancer.com/u/Whizpool.html Thanks, Zeeshan

Hello, How are you? I read your description and understand all I'm a full stack developer with PHP, Python, Node.js for Backend. And I already faced like yours with python. And now I fix that and works well I think we can fix that with my experience. I can provide you good result I think we can discuss about this project. Hope for your reply Regards Peng

I can optimize your query as per your requirements. I am a geolocation expert that can check out the issue in your system and see what can be done. thanks!

I have experience with Geoloaction. I can implement your idea. I have ever built the site for logging geo location . I am intereset with your project. Let's discuss in detail.

Hi there, We are a team based in Argentina with strong experience in software and mobile development. We are able to do your project starting ASAP. Do not hesitate to contact me if you are interested. You will not regret, Thank you very much, Best regards, Jorge.

Hi It's pleasure to meet you. I read complete project description and i understand the issue of geo location mysql query. We are certified web design and development company and working from last 7 years for clients from all over the world in variety of industries including following : Hospitality, marketing, real estate, retail, eCommerce etc. Actually you are following wrong way for getting geo location. We can use some other method that is light. Please initiate chat for further discussion to understand and negotiate cost and start work. I am looking forward to chat you. We will give our best service to you. Thanks Amit Kumar

I m able to do this job

Dear client! how are you? I have read your project description carefully. i can make good result you hope. I am a backend development master. I am very honest and diligent to client and I value credit. In particular, I place my highest priority on quality for your good business. My slogan is "Best quality + Fast speed = Your business success + Our pleasure" If you agree with my proposal, please feel free to contact me at any time. Hope you the best of luck in everything you do. See you soon, best regards...

Hi, Hope you are doing well! I am a professional Full-Stack developer with over 7 years experiences. This project is very interesting for me and makes me exciting. I am full-time available and can start work right now. Hope to work with you. Best Regards

Hello, I have extensive experience in working with MySQL queries and have done several Geolocation projects in the past. I am certain I can solve this for you. please send me a message so we can discuss further. I look forward to hearing from you. Caleb

Hello, I am very interested in this project as a senior web developer. As a fully experienced web developer, I have strong knowledge of Various Projects including Geolocation. Actually, I am good at ip detect, location detect & running app detect. Having enough knowledge, I think I can be a good fit for your project. Hope to work with you. Best Regards, David