I need a PHP and MYSQL expert to optimize my script and mysql database. The script was coded by another scriptlance programmer however they is busy with other projects. The script is pretty much 99% done and works great. It does have problems with the database query and creating output pages When there are large records in the database.
----- What the script does -----
The script is a directory that helps me manage a database of gallery links. Information about each link such as ID, display date, URL, description, format, niche, and thumbnails are saved in a mysql database. In the admin area, I bulk import gallery urls and descriptions, then the script creates thumbnails (Using GD or imagemagick) for each link, determines format, assigns display date, and saves the record into the database. (All this has already been coded and works perfect)
There are output pages for surfers to view gallery listings. In the admin area, I create a list of categories/keywords. For each keyword, the script creates a [url removed, login to view] page. when a surfer visits the [url removed, login to view] page, the script will query the database and find gallery records that contains description that matches that keyword and displays the results including thumbnails for that [url removed, login to view] page. The [url removed, login to view] also creates spanning pages such as [url removed, login to view], [url removed, login to view], etc. The [url removed, login to view] also makes a cache copy of itself which last for 6 hours. So when another surfer visits [url removed, login to view] the script will check if their is already a current cache version of the page and displays the cache page instead of having to do a database query each time. (Saves in excessive database queries)
Of course there are other features to the script such as templates, settings, gallery management, number of spanning pages, etc. This is just to give an idea of what the script is supposed to do.
----- The problem I am having -----
The script runs perfectly when the database is small. When I have lots of records, the script takes a long time to query the database and to create/display the output pages to surfers. Also when the database is large some spanning pages are missing and not created properly. Sometimes spanning pages are missing so the script didn't create all spanning [url removed, login to view] pages. I need the script optimized so that it runs better, queries faster, cache faster, and builds output pages faster; because the database will eventually have 500,000 to 1,000,000 records of gallery links.
The previous programmer said that the problem might be because the database has two tables primary "Gallery" table which has all the gallery link information and a relational "thumbnails" table which has thumbnail information for each gallery record. So the script has to query the database twice to retrieve gallery information.
----- What I need done -----
1. The mysql database right now has 4 tables.
ig_admin = Admin access information.
ig_settings = Settings for the script. (# of spanning pages, thumbnail folder, etc)
ig_gallery = Main gallery link information.
ig_thumbnail = Relational table that contains thumbnail information for each ig_gallery record. (Up to three thumbnails per gallery)
The "ig_gallery" and the "ig_thumbnail" table has a relationship where each ig_thumbnail record is a connected to a ig_gallery record. Having the script query the two tables when creating output pages really slows down the process of creating output pages. Instead of having two tables, I want to combine the two tables into just one table so when the script queries the database, it only has to do one query and displays the output/results faster. Basically the ig_thumbnail table will just be a column of the ig_gallery record. (This was suggested by the previous programmer) The script setups the database tables and stuff during install so all changes has to be from the beginning.
2. Add a few simple mysql query in the admin area to help manage the database. I need a query to search the database for records with description characters longer than X number and then truncate the remaining characters. For example if I want all gallery descriptions to be no longer than 50, I would query the database to find records with descriptions longer than 50 characters then the script will truncate/delete all characters after 50 and keep the first 50 characters as the description. Also a simple query to change all description text to lower case except first letter upper case.
3. check the [url removed, login to view] pages when the script creates it to see if there are any bugs and why some spanning pages are not created and if anything else can be done to optimized the script better.
My last programmer made a bid of $100 in 2 days. So I am looking to pay around that price range more or less. For more information please PMB me. P.S. The script is used for adult galleries so must be willing to work with adult content.