PHP: I have a simple pagination script which uses two queries.
MySQL: Using server version 4.1.25 - I have two tables (products, categories) with the item_num field tying them together to retrieve products via category and both have unique ID fields which are indexed and auto-incremented. They're both MyISAM types.
Query #1: SELECT COUNT(*) FROM rituals_collections, rituals_products WHERE rituals_collections.item_num = rituals_products.item_num AND rituals_collections.collection = '$currentCollection' $queryString ORDER BY rituals_products.id
This SHOULD grab only the record count using those filters so it doesn't need to get the data as well resulting in a longer load time. $queryString simply looks for any categories or sub-categories in the URL and if they're there it adds it to the filtering process. $currentCollection is what the user selected via drop-down.
Query #2: SELECT DISTINCT * FROM rituals_collections, rituals_products WHERE rituals_collections.item_num = rituals_products.item_num AND rituals_collections.collection = '$currentCollection' $queryString ORDER BY rituals_products.id LIMIT $offset, $rowsperpage
I think this is where the bottleneck is happening. I need DISTINCT because the admin can't eliminate his duplicates. The rest of the query is the same w/the exception of LIMIT $offset,$rowsperpage which limits the products for pagination purposes.
Database Screenshots
I have screenshots available, but since I'm a new user I can't post them yet. I can provide them afterwards I suppose.
Final Notes: I've included the entire script here: http://pastie.org/1239167 The two table setup is situated the way it is because of a 3rd party script my client uses and is unavoidable. And, this ties into the MODx CMS but should be pretty straightforward.
If anyone can help, I'd be eternally grateful. Thank you!