A是小表 3000条,B是大表 50万条
CREATE TABLE A (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`creator` varchar(24) NOT NULL,
`price` varchar(64) NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(1) not null,
PRIMARY KEY (`id`)
);
CREATE TABLE B (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`pid` bigint(20) NOT NULL,
`status` int not null,
`type` varchar(12) NOT NULL DEFAULT '0',
`quantity` int not null default 1,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
有这样的一句sql
SELECT * FROM A t1 INNER JOIN B t2 ON t2.pid = t1.id ORDER BY t1.status ASC, t2.ctime DESC LIMIT 0, 30;
如果不用 t1.status 排序,速度会快很多,加上之后就变慢了,要3s 才能查出来,这样的sql 该怎么优化了?