We have a large database that we need optimized to work much faster (about 80GB of data, 120,000,000 rows). The structure is below.

 

When we query a single row (LIMIT 1) or a small amount (i.e. LIMIT 0, 30) it runs very fast. But when ever we do something that needs to run through most of the table (like a specific select that does not exist) it takes a very long time. Also group by functions like count run slow.

 

All of our queries will be through PHP pages.

 

Ultimately we want to create browesable a directory.

 

Part of the work my entail actually importing the raw data into the MySql database. We have it broken down into about 1200 separate insert files. Right we have about ½ of the data imported so far.

 

The final result of this project will be us being able to run fast queries against the database to create a directory similar to the one on the lookupanyone.com website mentioned above. Name specific queries (ie when we are searching for only a single record) should render in the PHP in under a second. GROUP queries  should not take more then 10 seconds per page.

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • Blogosphere News
  • Blue Dot
  • Furl
  • Reddit
  • Slashdot