I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not.
Basically, I need to get a count of old and invalid entries per group, which is identified by a value of NULL or simply a month old. The selected data is going to be stored in a smaller table called "statistics". PHP is used for a cron script.
Now, the problem is I'm still a novice so I can't tell if the query is fast or slow. To finish the following query, it takes roughly 3 minutes, which according to my boss is slow and needs to be at least 50% faster, but I myself am not sure if anything can be done to speed it up. "cat_01" table has 50 mil row, the others have less then 2000.
I did add indexes to all used columns, which lowered the time from 6 to 3 minutes.
The query I'm using is as follows (I used pseudonyms for tables and columns, so don't mind the logic):
SELECT 'cat_01' as category, m.shop_id, o.name, count(m.shop_id) as total, tr2.traffic, IF(mm.bought IS NULL,0,mm.bought) as bought FROM cat_01 m JOIN shops o ON m.shop_id = o.id JOIN ( select mmm.shop_id, max(mmm.buy_date) as bought FROM cat_01 mmm GROUP BY mmm.shop_id ) mm ON o.id = mm.shop_id LEFT JOIN ( SELECT tr.shop_id, sum(tr.sales) AS traffic FROM ( SELECT mmmmm.shop_id, bs.sales, bs.order_id, bs.id FROM cat_01 mmmmm JOIN orders bs ON mmmmm.order = bs.order_id ) tr group by tr.shop_id ) tr2 ON tr2.shop_id = mm.shop_id WHERE (m.buy_date IS NULL) OR (m.buy_date < UNIX_TIMESTAMP()) GROUP BY m.shop_id
- Create table "cat01":
CREATE TABLE `cat_01` ( `id` int(11) NOT NULL AUTO_INCREMENT, `shop_id` int(11) DEFAULT '0', `buy_date` int(11) DEFAULT NULL, `order` int(11) DEFAULT '0', PRIMARY KEY (`id`), KEY `shop_id` (`shop_id`), KEY `buy_date` (`buy_date`), KEY `order` (`order`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- Create table "shops":
CREATE TABLE `shops` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- create table "orders":
CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NOT NULL, `sales` int(11) NOT NULL DEFAULT '0', `status` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `order_id` (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Here is the explain (I don't have access to the live data from home, so I used dummy rows, but rest is the same)
EDIT: added UNIX_TIMESTAMP() as an example
EDIT2: I ended up removing the subqueries from the SELECT and used them seperately, then using a PHP loop, I created the appropriate INSERT query by mixing the results together.
P.S. I can't seem to find a direct example of how sql code formatting on sctackechange looks, so I apologize if it ends up garbled, I'll try to fix it shortly.
I do have an EXPLAIN result but not sure how to format that, might end up posting an image later.
If you need some particular info, feel free to ask, I myself am not sure what I should be looking at.