(This is to address the slow queries.)
SELECT COUNT(*) as `listings2`
FROM `ppb_listings`
WHERE end_time > NOW()
AND deleted = N
AND start_time < NOW()
Needs
INDEX(deleted, end_time, start_time),
INDEX(deleted, start_time, end_time)
Neither is really optimal, but they are 'covering', which helps some. The optimizer will dynamically pick which is likely to be better.
SELECT `value` as `file_path`
FROM `ppb_listings_media`
WHERE `value` = 'S'
UNION
SELECT `logo_path` as `file_path`
FROM `ppb_categories`
WHERE `logo_path` = 'S'
UNION
SELECT `content` as `file_path`
FROM `ppb_advertising`
WHERE `content` = 'S'
AND `type` = 'S'
UNION
SELECT `value` as `file_path`
FROM `ppb_settings`
WHERE `value` = 'S'
AND `name` = 'S'
UNION
SELECT `store_settings` as `file_path`
FROM `ppb_users`
WHERE `store_settings` LIKE 'S'
UNION defaults to UNION DISTINCT; change to UNION ALL if practical, since it is faster.
Do you have simple/composite indexes on each table? For example, ppb_settings needs INDEX(value, name) or INDEX(name, value).
SELECT listings.*
FROM ppb_listings AS listings
WHERE (closed = N)
AND (deleted = N)
AND (draft = N)
AND (start_time < now())
AND (end_time < now())
AND (end_time is not null)
LIMIT N FOR
# and
UPDATE ppb_listings SET closed = 'S', updated_at = now()
WHERE (closed = N)
AND (deleted = N)
AND (draft = N)
AND (start_time < now())
AND (end_time > now()
OR end_time is null
)
INDEX(closed, deleted, draft, start_time, end_time) (or any of certain rearrangements of such.)
UPDATE ppb_listings SET deleted = 'S'
WHERE (end_time is not null)
AND (deleted = N)
AND (closed = N)
AND (end_time < (now() - interval N day))
INDEX(deleted, closed, end_time) -- note that this index and the previous one cannot be combined because end_time is checked in a "range". Also you can probably remove (end_time is not null) since the other test will fail anyway.
SELECT listings.*
FROM ppb_listings AS listings
WHERE (counted_at is null
OR counted_at < IF(updated_at is null, created_at, updated_at)
)
LIMIT N
Which N do you want? Perhaps you need an ORDER BY? INDEX(counted_at) might help.
SELECT categories.*
FROM ppb_categories AS categories
WHERE (enable_auctions = N)
AND (user_id is null)
AND (parent_id is null)
ORDER BY order_id ASC, name ASC
INDEX(enable_auctions, user_id, parent_id, order_id, name) -- the first 3 in any order; the last 2 must follow and must be in that order.
Those first few probably cover 99% of the activity on the server. Some of my suggestions will help more than others. If you need to discuss any of them, please start another thread, and supply the query, EXPLAIN SELECT ... and SHOW CREATE TABLE.
Those 7 looked like simple cases of "need the right index". If you already have the right index, then perhaps there is no way to improve it. At that point, you need to ask "Does the application really need this query? Or can we change to something else?"