mysql对于大字段的查询慢的处理

有两个表

create table e_question (
   question_id          char(32)             not null,
   topic_id             char(32)             null,
   title                varchar(1000)        null,
   answer               varchar(500)        null,
   answer_analysis      varchar(1500)        null,
   question_type        varchar(50)          null,
   score                numeric(20,2)        null,
   seq                  numeric(5,0)         null,
   institution_code     varchar(50)          null,
   create_date          varchar(20)          null,
   create_user          varchar(50)          null,
   constraint PK_E_QUESTION primary key (question_id)
)

create table e_pick_topic (
   pick_topic_id        char(32)             not null,
   task_id              char(32)             null,
   topic_id             char(32)             null,
   institution_code     varchar(50)          null,
   create_date          varchar(20)          null,
   create_user          varchar(50)          null,
   constraint PK_E_PICK_TOPIC primary key (pick_topic_id)
)

在表e_question有3个大字段，title, answer, anser_analysis,所以在关联查询的时候会很慢，如：

SELECT eq.topic_id, eq.question_id, eq.answer, eq.answer_analysis, eq.question_type, eq.question_type, eq.seq, eq.score   
FROM e_question eq left join e_pick_topic ept  
on ept.topic_id=eq.topic_id where ept.task_id='758BDAB8751CFEB400007936421123' AND ept.institution_code='test';

这个在环境中需要10来秒的时间，但是去掉三个字段就飞快。

在我调试的时候，发现是因为大字段的问题，在调试中将语句改成

[b]方式1：[/b]

1步骤：

select topic_id from e_pick_topic where task_id='758BDAB8751CFEB400007936421123' and institution_code='test';

2.步骤：

select eq.topic_id, eq.question_id, eq.title, eq.answer, eq.answer_analysis, eq.question_type, eq.seq, eq.score from e_question eq
where topic_id in ('7564888E751CFEB400003750162951','7564889E751CFEB400003756561015','7564889E751CFEB400003762107475','7564889E751CFEB400003768978194','756488AD751CFEB400003774710532','756488AD751CFEB400003780898187','756488AD751CFEB400003786711844','756488AD751CFEB400003792199143','756488AD751CFEB400003798114697','756488BD751CFEB400003804334112','756488BD751CFEB400003810603026','756488BD751CFEB400003816274746','756488BD751CFEB400003822964006','756488CD751CFEB400003828924522','756488CD751CFEB400003834876747','756488CD751CFEB400003840513031','756488CD751CFEB400003846430043','756488CD751CFEB400003852596200','756488DC751CFEB400003858275793','756488DC751CFEB400003864456005','756488DC751CFEB400003870670344','756488DC751CFEB400003876565867','756488EC751CFEB400003882613793','756488EC751CFEB400003888361271','756488EC751CFEB400003894586024','756488EC751CFEB400003900327297','756488EC751CFEB400003906720322','756488FB751CFEB400003912456060','756488FB751CFEB400003918971501','756488FB751CFEB400003924468869','756488FB751CFEB400003930805450','7564890B751CFEB400003936807772','7564890B751CFEB400003942820780','7564890B751CFEB400003948938316','7564890B751CFEB400003954866911','7564890B751CFEB400003960142081','7564891B751CFEB400003966566807','7564891B751CFEB400003972572799','7564891B751CFEB400003978699664','7564891B751CFEB400003984950892','7564892A751CFEB400003990976715','7564892A751CFEB400003994142222','7564892A751CFEB400003998183213','7564892A751CFEB400004002545282','7564892A751CFEB400004006418623','7564892A751CFEB400004010860697','7564892A751CFEB400004014881827','7564893A751CFEB400004018735173','7564893A751CFEB400004022928694','7564893A751CFEB400004026929017','7564893A751CFEB400004030733099','7564893A751CFEB400004034392529','7564893A751CFEB400004038431427','7564893A751CFEB400004042168002','75648949751CFEB400004046323486','75648949751CFEB400004050809106','75648949751CFEB400004054675041','75648949751CFEB400004058761562','75648949751CFEB400004062499206','75648949751CFEB400004066293360','75648949751CFEB400004070771754','75648959751CFEB400004096265694')

这样总共加起来的时间还不用0.06秒的时间，
所以得到的结论是对需要查询大字段，不应该做关联查询，应该使用分开独立查询。

[b]方式2[/b]

将字段的类型都改成text类型。这样关联查询的时候，消耗的时间为0.3到0.4秒之间。

使用分开查询的话，也是在0.06秒之前。

[b]可以建不管是使用varchar 还是text类型，如果需要查询大字段出来，就不应该使用关联查询来实现。而是使用分开查询[/b]