mysql - SQL SELECT and PHP retrive speed INT vs TINYINT vs VARCHAR vs ENUM? -

i'm in process of creating table display records , made me wonder.

if store, 'eating habits' has make (vegetarian, non-vegetarian , eggetarian), make difference on query speed if store 'eating_habits' int or tinyint or varchar or enum type.

so using int [approach 1]

// select records   select * table eating_habits = 2 , ...;  //for display in php   foreach($all_records $key => $val) { if($val['eating_habits'] == 1)     echo 'vegetarian';  elseif($val['eating_habits'] == 2)     echo 'non-vegetarian'; else  echo 'eggetarian'; } 

faster/slower - using tinyint [approach 2]

 // script same above aproach 1   .... 

faster/slower - using int or tinyint join****[approach 3]

// select records    select *.t1  table1 t1 join table2 t2 on ti.eat_habit_id=t2.eating_habits   t2.eating_habits = 2 , ...; //for display in php    foreach($all_records $key => $val) {   echo $val['eating_habits'];   } 

faster/slower - using varchar [approach 4]

// select records   select * table eating_habits = 'non-vegetarian' , ...;  //for display in php   foreach($all_records $key => $val) { echo $val['eating_habits']; } 

faster/slower - using enum [approach 5]

  // script same above approach 3   ..... 

from above 5 approach give me faster result search, fetch , display records in php page ?

the speed difference insignificant. biggest cost reading record. that, shorter records usually better performance. 1-byte enum suggested smallest.

a drawback enum if add new option, need alter table.

similarly 1-byte tinyint marginally faster 4-byte int.

if going have few thousand rows, make varchar(..) , don't worry it. if expect have hundreds of millions of rows, do pick datatype carefully.