k2host
11-01-2005, 06:40 PM
First, don't reply if you *think* you can help.. I'm very experienced with PHP/MySQL, but what I'm looking for is someone who knows MySQL inside and out, specifically the new MySQL 5.x with views and stored procedures.
I have a project that relies an a very large database (40+ GB) that is growing daily. Because of the database structure, I've had to design some denormalized tables to allow for fast querying of the data via a web interface. (It's a very large website) However, the problems I've run into are as follows:
1) Updating the static, denormalized, tables takes a number of hours and puts quite a load on the database.. making it virtually unusable while these very much needed tables are being updated.
2) Fulltext searches on some of the tables take much too long (think 30+ seconds, when I need them to take < 1 second).
The professional who takes on this project should be able to make and impliment a database design that will provide the same functionality that my denormalized tables currently provide, with the exception that there will be NO need to ever update the tables via a script as they will be updated live as new data is added to the source tables of the summary tables. Additionally, query time must remain at an optimized level (< 1 second page loads).
My recommendation is to make use of stored procedures and to optimize my already created indexes to make improvements and possibly make use of views. I expect the professional who takes on this job to provide the optimal solution along with their reasoning.
Additionally, the completed project would require providing optimization to the full-text searches that are too slow.
Lastly, if the professional could re-design the summary tables in a way that reduces disk space usage that would be optimal.. as currently data is duplicated multiple times across tables to provide the increased performance.
If you are interested in taking on this project please contact me via private message or email, please include your related experience and any questions you'll need answered to provide me an accurate quote for this project.
Thanks,
Kurt
I have a project that relies an a very large database (40+ GB) that is growing daily. Because of the database structure, I've had to design some denormalized tables to allow for fast querying of the data via a web interface. (It's a very large website) However, the problems I've run into are as follows:
1) Updating the static, denormalized, tables takes a number of hours and puts quite a load on the database.. making it virtually unusable while these very much needed tables are being updated.
2) Fulltext searches on some of the tables take much too long (think 30+ seconds, when I need them to take < 1 second).
The professional who takes on this project should be able to make and impliment a database design that will provide the same functionality that my denormalized tables currently provide, with the exception that there will be NO need to ever update the tables via a script as they will be updated live as new data is added to the source tables of the summary tables. Additionally, query time must remain at an optimized level (< 1 second page loads).
My recommendation is to make use of stored procedures and to optimize my already created indexes to make improvements and possibly make use of views. I expect the professional who takes on this job to provide the optimal solution along with their reasoning.
Additionally, the completed project would require providing optimization to the full-text searches that are too slow.
Lastly, if the professional could re-design the summary tables in a way that reduces disk space usage that would be optimal.. as currently data is duplicated multiple times across tables to provide the increased performance.
If you are interested in taking on this project please contact me via private message or email, please include your related experience and any questions you'll need answered to provide me an accurate quote for this project.
Thanks,
Kurt