MySQL INDEX

最新推荐文章于 2024-10-18 11:30:59 发布
原创最新推荐文章于 2024-10-18 11:30:59 发布 · 476 阅读
CC 4.0 BY-SA版权
-- * test index with 'and & or' - begin *

-- type: ref
explain
select * from student where name = '' and class_id = '';

-- type: all, extra: using where
explain
select * from student where name = '' or class_id = '';

-- type: ref
explain
select * from student where class_id = '' and name = '';

-- type: all, extra: using where
explain
select * from student where class_id = '' or name = '';

-- * test index with 'and & or' - end *



-- * test index with 'order by' - begin *

-- type: ref
explain
select * from student where name = '' and class_id = '' order by name;

-- type: ref, extra: using index condition & using filesort
explain
select * from student where name = '' and class_id = '' order by class_id;

-- type: ref
explain
select * from student where class_id = '' and name = '' order by name;

-- type: ref, extra: using index condition & using filesort
explain
select * from student where class_id = '' and name = '' order by class_id;

-- * test index with 'order by' - end *



-- * test index with 'group by' - begin *

-- type: ref
explain
select * from student where name = '' and class_id = '' group by name;

-- type: ref, extra: using index condition & using temporary & using filesort
explain
select * from student where name = '' and class_id = '' group by class_id;

-- type: ref
explain
select * from student where class_id = '' and name = '' group by name;

-- type: ref, extra: using index condition & using temporary & using filesort
explain
select * from student where class_id = '' and name = '' group by class_id;

-- * test index with 'group by' - end *



-- * test index with 'group by & order by' - begin *

-- type: ref
explain
select * from student where name = '' and class_id = '' group by name order by name;

-- type: ref
explain
select * from student where name = '' and class_id = '' group by name order by class_id;

-- type: ref, using index condition & using temporary
explain
select * from student where name = '' and class_id = '' group by class_id order by name;

-- type: ref, extra: using index condition & using temporary & using filesort
explain
select * from student where name = '' and class_id = '' group by class_id order by class_id;



-- type: ref
explain
select * from student where class_id = '' and name = '' group by name order by name;

-- type: ref
explain
select * from student where class_id = '' and name = '' group by name order by class_id;

-- type: ref, using index condition & using temporary
explain
select * from student where class_id = '' and name = '' group by class_id order by name;

-- type: ref, extra: using index condition & using temporary & using filesort
explain
select * from student where class_id = '' and name = '' group by class_id order by class_id;

-- * test index with 'group by & order by' - end *