Get a Freelance Job

I have been optimizing some mysql queries, but I feel like they can be optimized more.

I have experimented with many possibilities including indexes. I am not a MYSQL newbie. I need a mysql expert to make the queries lightning fast.

Maximum of 5 queries of 620 characters.

I have been optimizing some mysql queries, but I feel like they can be optimized more.

I have experimented with many possibilities including indexes. I am not a MYSQL newbie. I need a mysql expert to make the queries lightning fast.

Maximum of 5 queries of 620 characters.

Perl and MySQL coding. Increase the speed of Mysql Queries

I have a site that functions (PERL), but using MySQL PERL DBI search FULLTEXT BOOLEAN is slow, some query’s words slower than others. Some query’s take 25 seconds to load a page. The same happens in phpMyAdmin with the same SQL statements.

I need help to increase the Query speed to the minimum possible.
I need to be able to achieve this with Shared hosting, (so any coding should have minimal impact on the host servers), using cPanel and phpMyAdmin.

I will require explanations of any changes and why they work faster.
The database in use at this time is about 1Mb, about 2Million rows), but it will need to be over 2gb of data once the code functions at full speed.

Any charges quoted must be full costs and complete to fix the slow queries. No extra charges are available. That is Full costs MUST be fully known and complete before any job is posted for me.

Data is poor quality e.g. For example not normalised, at this time and uses some REAL variables for BOOLEAN values.

Full and complete total costs/pricing must be stated.

Perl and MySQL coding. Increase the speed of Mysql Queries

I have a site that functions (PERL), but using MySQL PERL DBI search FULLTEXT BOOLEAN is slow, some query’s words slower than others. Some query’s take 25 seconds to load a page. The same happens in phpMyAdmin with the same SQL statements.

I need help to increase the Query speed to the minimum possible.
I need to be able to achieve this with Shared hosting, (so any coding should have minimal impact on the host servers), using cPanel and phpMyAdmin.

I will require explanations of any changes and why they work faster.
The database in use at this time is about 1Mb, about 2Million rows), but it will need to be over 2gb of data once the code functions at full speed.

Any charges quoted must be full costs and complete to fix the slow queries. No extra charges are available. That is Full costs MUST be fully known and complete before any job is posted for me.

Data is poor quality e.g. For example not normalised, at this time and uses some REAL variables for BOOLEAN values.

Iam looking for a MYSQL guru to optimize a couple of slow queries, please don’t bid on this project unless you are a expert in mysql and can dramatically reduce the time these queries take.

Below are the problem queries taken from the slow query log.

# Query_time: 8 Lock_time: 0 Rows_sent: 8 Rows_examined: 378201
SELECT *, COUNT(file_id) AS image_count FROM uploader_userfolders JOIN uploader_userfiles USING(folder_id) WHERE file_isimage=1 AND folder_i$

# Query_time: 16 Lock_time: 3 Rows_sent: 30 Rows_examined: 682764
SELECT pm.pfile_id, pm.mfile_id, pu.*, mf.file_description, mf.file_extension, mf.file_date, mf.file_key, mf.userid, mf.folder_id,uf.*, MAKE$
MAKE_SET( 1 |2, COUNT(mf.file_id), COUNT(pf.file_id) ) AS image_count,
MAKE_SET( 1 |2, pf.file_name, mf.file_name ) AS file_name,
MAKE_SET( 1 |2, pf.file_location, mf.file_location ) AS file_location,
MAKE_SET( 1 |2, pf.file_size, mf.file_size ) AS file_size,
MAKE_SET( 1 |2, pf.file_views, mf.file_views ) AS file_views,
MAKE_SET( 1 |2, pf.file_lastview, mf.file_last_view ) AS file_lastview,
MAKE_SET( 1 |2, pf.file_isimage, mf.file_isimage ) AS file_isimage,
MAKE_SET( 1 |2, uf.folder_name, pu.upload_name ) AS set_name,
ud.username,ud.avatar
FROM uploader_pmfiles AS pm
LEFT JOIN uploader_puploads AS pu ON ( pm.pfile_id = pu.upload_id )
LEFT JOIN uploader_pfiles as pf USE INDEX(upload_id) USING(upload_id)
LEFT JOIN uploader_userfiles AS mf ON ( pm.mfile_id = mf.file_id )
LEFT JOIN uploader_userfolders AS uf ON ( mf.folder_id = uf.folder_id )
LEFT JOIN uploader_users AS ud ON ( mf.userid = ud.userid )
WHERE (pu.upload_ispublic = 1 OR uf.folder_ispublic = 1)
GROUP BY mf.folder_id, pu.upload_id
ORDER BY pm.file_date DESC
LIMIT 0, 30;

I have a joke database with a pretty straightforward setup. Somehow my “view joke” and “view category” (ie – blonde jokes etc.) pages stopped working correctly, and I simply need a couple basic queries to toss into my template so that the correct content appears.

Possibly due to some heavy customization before, but for this project, all the side stuff (built in on-the-fly meta keywords etc) don’t really matter as the most important thing is, of course, getting the page to simply show the appropriate joke or category info. As it stands right now, the template looks fine, but the space where the joke should be appearing is simply blank.

Should be a breeze for any PHP and MySQL pro.