I have this script I made that first creates a temporary table containing all the products for the current category.
Then I have a search class that creates a temporary table and inserts products into it from various different querys on the table it is searching (the temp table for the category).
This is so when looking at a product category you can do a search for products withing that category.
Most the time this is fine, but if the category has too many products on it, the entire site starts to lag when you do a search. When I say entire site I mean if I close the window and open a different page on the site it will still be lagging for a couple minutes.
I don't really want to post any code at the moment because there is a lot and I am not sure which parts would need to be posted.
But I am wondering if there is like some sort of mysql profiler that could tell me what is going on so I can address it?
update
I was asked to post the query in question and since there are several I will post them with descriptions of what they are for:
#create category temp table
CREATE TEMPORARY TABLE cat_prod_table
SELECT AVG(product_stat / DATEDIFF(NOW(), product_date_added)) as avg_hits, cart_product.*
FROM cart_product
WHERE (product_brand = "penn state")
AND product_available = "yes"
AND include_in_category = "yes"
GROUP BY product_id
#This is how i create the temporary table to perform a search on
$this->tempTable = $tempTableName;
$qry = 'CREATE TEMPORARY TABLE '.$this->tempTable.' (id INT, score DECIMAL(12,10), UNIQUE KEY (id))';
# I do this full text search
INSERT INTO '.$this->tempTable.' (id, score)
SELECT '.$this->searchTable.'.product_id as id,
MATCH (
product_name, product_description, product_brand, metal_type, primary_stone, product_type, product_type_sub, product_series, primary_stone_sub
)
AGAINST (
"'.$str.'"
) as score
FROM '.$this->searchTable.'
WHERE product_available = "yes"
HAVING score > 0
#I do this incase the search exactly matches the product id
INSERT INTO '.$this->tempTable.' (id, score)
SELECT product_id, 50
FROM '.$this->searchTable.'
WHERE product_id = "'.$str.'"
AND product_available = "yes"
ON DUPLICATE KEY UPDATE score = score + 50
#I do this incase the search exactly matches the style number
INSERT INTO '.$this->tempTable.' (id, score)
SELECT product_id, 40
FROM '.$this->searchTable.'
WHERE product_manufacturer_num LIKE "%'.$str.'%"
AND product_available = "yes"
ON DUPLICATE KEY UPDATE score = score + 40
#I do a full text search in boolean mode so terms that are in more the 50% of all rows are included
INSERT INTO '.$this->tempTable.' (id, score)
SELECT '.$this->searchTable.'.product_id as id, 1 AS score
FROM '.$this->searchTable.'
WHERE product_available = "yes"
AND MATCH (
product_name, product_description, product_brand, metal_type, primary_stone, product_type, product_type_sub, product_series, primary_stone_sub
)
AGAINST (
"'.$words.'"
IN BOOLEAN MODE
)
ON DUPLICATE KEY UPDATE score = score + 1
#I do this to so that items that are not rings will score low on a search for rings
UPDATE '.$this->tempTable.'
LEFT JOIN '.$this->searchTable.' p
ON '.$this->tempTable.'.id = p.product_id
SET '.$this->tempTable.'.score = '.$this->tempTable.'.score / 2
WHERE MATCH (
p.product_name, p.product_description, p.product_brand, p.metal_type, p.primary_stone, p.product_type, p.product_type_sub, p.product_series, p.primary_stone_sub
)
AGAINST (
"bracelet earrings neckpiece necklace charm pendant watch"
IN BOOLEAN MODE
)