My website - an online store. I need to display data from the table of categories. And count of items in this category.
Number of rows in the table ITEMS about 6700.
Number of rows in the table CATEGORY 123
I use this query:
SELECT c.* , COUNT( i.id ) count FROM category c LEFT JOIN items i ON
i.catid = c.catid GROUP BY c.id
This query is performed for more than 4 seconds.
The result of the EXPLAIN: http://i.gyazo.com/c71c43af2719010cb5c4a2ad1d8cefd8.png
table category:
CREATE TABLE IF NOT EXISTS `category ` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catid` varchar(16) NOT NULL,
`url` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
table items:
CREATE TABLE IF NOT EXISTS ` items ` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catid` int(11) NOT NULL,
`link` varchar(255) NOTNULL,
`title` varchar(255) NOT NULL,
`images` text NOT NULL,
`price` double NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
);
How can I optimize it? please, help
You are comparing category.catid varchar(16) with items.catid int(11) will be more faster if you compare int = int deducing that catid is just numbers you can try alter the catid column in category table
alter table category modify column catid int(11) NOT NULL;
test this code above.
NOTE: if you have any category.catid greater than 2147483647 you must to change to bigint instead of int.
And if both catid are not null, i can understand that no have itens without a catid, then you can change LEFT JOIN items i ON i.catid = c.catid by INNER JOIN items i ON i.catid = c.catid i dont sure if this will be more fast, but try it.
The ideal was catid be the primary key of category and items.catid a foreign key of category.catid. But to do this, you will need to remove primary key to category.id and add to category.catid, after you can add foreign key items.catid. But just comparing int = int will do diference.