MySQL Forums
Forum List  »  Performance

Handling Large Datasets
Posted by: Brandon Orth
Date: February 17, 2011 08:53PM

Hello All,

(using php,mysql,jquery,javascript)
I have a database of about 1 million products. I will be using a jquery/ajax server call to retrieve records based on a filters.
scenario: Search for a hardrive within 5 miles of my house. When the results are displayed, show filters like hard drive size, external, internal, etc.. Use the jquery/ajax call to return filtered results on the fly (user doesn't leave page). Filters are toggled. A user can view the product or navigate elsewhere and return to the search results page where they left off.

As of now, it will query through a million records to find all products within a given radius. The typical range of products returned are about 1000-10000. I need someway to store these records for the users session while they browsing the site. I can then query a max of 10k records to filter by rather than 1 million records. This will help reduce the strain on the system and use less resources.

1. Using a temporary table in mysql is valid only while the connection is open to the server. I need it to last as long as the user is on the site.
2. Creating a normal table to store the initial results will become too much. Too many tables to manage. There could be as many as 8000 tables at a given time for 8000 visitors and this will slow performance overall.
3. Saving the results as an xml,json would be fine. However, finding a file with up to 8000 files in a directory at a given time can increase the time it takes to find the file. Especially cumbersome when you are creating, updating, or deleting files. This would also require a cron job to manage the deletion of expired files. Creating multiple directories with less files in each may also become to much to manage.

What is the best way to approach this challenge for performance?

Options: ReplyQuote

Written By
Handling Large Datasets
February 17, 2011 08:53PM
February 18, 2011 10:04AM
February 18, 2011 09:46PM
February 19, 2011 09:46AM
March 21, 2011 04:17AM
March 21, 2011 09:17AM
February 18, 2011 03:39AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.