MySQL Icon

When to Store Data in a Table vs Using an Array

I was coding out a basic contact form today with the usual name, address, email etc. I was building out the country select menu and noticed I already had code for building it via an array and building it from a database table. So I decided to put the two up against each other and see which one was faster.

Most of you probably already can guess or know which one would win out but lets take a look. Here is my table:

CREATE TABLE `countries` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
);

Nothing special, just basic here. Now here is my array of countries:

// Country Array
$country[] = "Afghanistan|Afghanistan";
$country[] = "Albania|Albania";
$country[] = "Algeria|Algeria";
$country[] = "American Samoa|American Samoa";
$country[] = "Andorra|Andorra";
$country[] = "Angola|Angola";
$country[] = "Anguilla|Anguilla";
$country[] = "Antarctica|Antarctica";
$country[] = "Antigua And Barbuda|Antigua And Barbuda";
$country[] = "Argentina|Argentina";
$country[] = "Armenia|Armenia";
$country[] = "Aruba|Aruba";
$country[] = "Australia|Australia";
$country[] = "Austria|Austria";
$country[] = "Azerbaijan|Azerbaijan";
$country[] = "Bahamas|Bahamas";
$country[] = "Bahrain|Bahrain";
$country[] = "Bangladesh|Bangladesh";
$country[] = "Barbados|Barbados";
$country[] = "Belarus|Belarus";
$country[] = "Belgium|Belgium";
$country[] = "Belize|Belize";
$country[] = "Benin|Benin";
$country[] = "Bermuda|Bermuda";
$country[] = "Bhutan|Bhutan";
$country[] = "Bolivia|Bolivia";
$country[] = "Bosnia And Herzegovina|Bosnia And Herzegovina";
$country[] = "Botswana|Botswana";
$country[] = "Bouvet Island|Bouvet Island";
$country[] = "Brazil|Brazil";
$country[] = "British Indian Ocean Territory|British Indian Ocean Territory";
$country[] = "Brunei Darussalam|Brunei Darussalam";
$country[] = "Bulgaria|Bulgaria";
$country[] = "Burkina Faso|Burkina Faso";
$country[] = "Burundi|Burundi";
$country[] = "Cambodia|Cambodia";
$country[] = "Cameroon|Cameroon";
$country[] = "Canada|Canada";
$country[] = "Cape Verde|Cape Verde";
$country[] = "Cayman Islands|Cayman Islands";
$country[] = "Central African Republic|Central African Republic";
$country[] = "Chad|Chad";
$country[] = "Chile|Chile";
$country[] = "China|China";
$country[] = "Christmas Island|Christmas Island";
$country[] = "Cocos (Keeling) Islands|Cocos (Keeling) Islands";
$country[] = "Colombia|Colombia";
$country[] = "Comoros|Comoros";
$country[] = "Congo|Congo";
$country[] = "Cook Islands|Cook Islands";
$country[] = "Costa Rica|Costa Rica";
$country[] = "Croatia|Croatia";
$country[] = "Cuba|Cuba";
$country[] = "Cyprus|Cyprus";
$country[] = "Czech Republic|Czech Republic";
$country[] = "Cete D'ivoire|Cete D'ivoire";
$country[] = "Denmark|Denmark";
$country[] = "Djibouti|Djibouti";
$country[] = "Dominica|Dominica";
$country[] = "Dominican Republic|Dominican Republic";
$country[] = "Ecuador|Ecuador";
$country[] = "Egypt|Egypt";
$country[] = "El Salvador|El Salvador";
$country[] = "Equatorial Guinea|Equatorial Guinea";
$country[] = "Eritrea|Eritrea";
$country[] = "Estonia|Estonia";
$country[] = "Ethiopia|Ethiopia";
$country[] = "Falkland Islands|Falkland Islands";
$country[] = "Faroe Islands|Faroe Islands";
$country[] = "Fiji|Fiji";
$country[] = "Finland|Finland";
$country[] = "France|France";
$country[] = "French Guiana|French Guiana";
$country[] = "French Polynesia|French Polynesia";
$country[] = "French Southern Territories|French Southern Territories";
$country[] = "Gabon|Gabon ";
$country[] = "Gambia|Gambia";
$country[] = "Georgia|Georgia";
$country[] = "Germany|Germany";
$country[] = "Ghana|Ghana";
$country[] = "Gibraltar|Gibraltar";
$country[] = "Greece|Greece";
$country[] = "Greenland|Greenland";
$country[] = "Guadeloupe|Guadeloupe";
$country[] = "Guam|Guam";
$country[] = "Guatemala|Guatemala";
$country[] = "Guinea|Guinea";
$country[] = "Guinea-Bissau|Guinea-Bissau";
$country[] = "Guyana|Guyana";
$country[] = "Haiti|Haiti";
$country[] = "Honduras|Honduras";
$country[] = "Hong Kong|Hong Kong";
$country[] = "Hungary|Hungary";
$country[] = "Iceland|Iceland";
$country[] = "India|India";
$country[] = "Indonesia|Indonesia";
$country[] = "Iran|Iran";
$country[] = "Iraq|Iraq";
$country[] = "Ireland|Ireland";
$country[] = "Israel|Israel";
$country[] = "Italy|Italy";
$country[] = "Jamaica|Jamaica";
$country[] = "Japan|Japan";
$country[] = "Jordan|Jordan";
$country[] = "Kazakhstan|Kazakhstan";
$country[] = "Kenya|Kenya";
$country[] = "Kiribati|Kiribati";
$country[] = "Korea, North|Korea, North";
$country[] = "Korea, South|Korea, South";
$country[] = "Kuwait|Kuwait";
$country[] = "Kyrgyzstan|Kyrgyzstan";
$country[] = "Lao People's Republic|Lao People's Republic";
$country[] = "Latvia|Latvia";
$country[] = "Lebanon|Lebanon";
$country[] = "Lesotho|Lesotho";
$country[] = "Liberia|Liberia";
$country[] = "Libyan Arab Jamahiriya|Libyan Arab Jamahiriya";
$country[] = "Liechtenstein|Liechtenstein";
$country[] = "Lithuania|Lithuania";
$country[] = "Luxembourg|Luxembourg";
$country[] = "Macao|Macao";
$country[] = "Macedonia|Macedonia";
$country[] = "Madagascar|Madagascar";
$country[] = "Malawi|Malawi";
$country[] = "Malaysia|Malaysia";
$country[] = "Maldives|Maldives";
$country[] = "Mali|Mali";
$country[] = "Malta|Malta";
$country[] = "Marshall Islands|Marshall Islands";
$country[] = "Martinique|Martinique";
$country[] = "Mauritania|Mauritania";
$country[] = "Mauritius|Mauritius";
$country[] = "Mayotte|Mayotte";
$country[] = "Mcdonald Islands|Mcdonald Islands";
$country[] = "Mexico|Mexico";
$country[] = "Micronesia, Fed. States|Micronesia, Fed. States";
$country[] = "Moldova, Republic Of|Moldova, Republic Of";
$country[] = "Monaco|Monaco";
$country[] = "Mongolia|Mongolia";
$country[] = "Montserrat|Montserrat";
$country[] = "Morocco|Morocco";
$country[] = "Mozambique|Mozambique";
$country[] = "Myanmar|Myanmar";
$country[] = "Namibia|Namibia";
$country[] = "Nauru|Nauru";
$country[] = "Nepal|Nepal";
$country[] = "Netherlands Antilles|Netherlands Antilles";
$country[] = "Netherlands|Netherlands";
$country[] = "New Caledonia|New Caledonia";
$country[] = "New Zealand|New Zealand";
$country[] = "Nicaragua|Nicaragua";
$country[] = "Niger|Niger";
$country[] = "Nigeria|Nigeria";
$country[] = "Niue|Niue";
$country[] = "Norfolk Island|Norfolk Island";
$country[] = "Northern Mariana Islands|Northern Mariana Islands";
$country[] = "Norway|Norway";
$country[] = "Oman|Oman";
$country[] = "Pakistan|Pakistan";
$country[] = "Palau|Palau";
$country[] = "Palestinian, Occupied|Palestinian, Occupied";
$country[] = "Panama|Panama";
$country[] = "Papua New Guinea|Papua New Guinea";
$country[] = "Paraguay|Paraguay";
$country[] = "Peru|Peru";
$country[] = "Philippines|Philippines";
$country[] = "Pitcairn|Pitcairn";
$country[] = "Poland|Poland";
$country[] = "Portugal|Portugal";
$country[] = "Puerto Rico|Puerto Rico";
$country[] = "Qatar|Qatar";
$country[] = "Romania|Romania";
$country[] = "Russian Federation|Russian Federation";
$country[] = "Rwanda|Rwanda";
$country[] = "Saint Helena|Saint Helena";
$country[] = "Saint Kitts And Nevis|Saint Kitts And Nevis";
$country[] = "Saint Lucia|Saint Lucia";
$country[] = "Saint Pierre And Miquelon|Saint Pierre And Miquelon";
$country[] = "St Vincent And The Grenadines|St Vincent And The Grenadines";
$country[] = "Samoa|Samoa";
$country[] = "San Marino|San Marino";
$country[] = "Sao Tome And Principe|Sao Tome And Principe";
$country[] = "Saudi Arabia|Saudi Arabia";
$country[] = "Senegal|Senegal";
$country[] = "Serbia And Montenegro|Serbia And Montenegro";
$country[] = "Seychelles|Seychelles";
$country[] = "Sierra Leone|Sierra Leone";
$country[] = "Singapore|Singapore";
$country[] = "Slovakia|Slovakia";
$country[] = "Slovenia|Slovenia";
$country[] = "Solomon Islands|Solomon Islands";
$country[] = "Somalia|Somalia";
$country[] = "South Africa|South Africa";
$country[] = "South Georgia|South Georgia";
$country[] = "Spain|Spain";
$country[] = "Sri Lanka|Sri Lanka";
$country[] = "Sudan|Sudan";
$country[] = "Suriname|Suriname";
$country[] = "Svalbard And Jan Mayen|Svalbard And Jan Mayen";
$country[] = "Swaziland|Swaziland";
$country[] = "Sweden|Sweden";
$country[] = "Switzerland|Switzerland";
$country[] = "Syrian Arab Republic|Syrian Arab Republic";
$country[] = "Taiwan|Taiwan";
$country[] = "Tajikistan|Tajikistan";
$country[] = "Tanzania|Tanzania";
$country[] = "Thailand|Thailand";
$country[] = "Timor-Leste|Timor-Leste";
$country[] = "Togo|Togo";
$country[] = "Tokelau|Tokelau";
$country[] = "Tonga|Tonga";
$country[] = "Trinidad And Tobago|Trinidad And Tobago";
$country[] = "Tunisia|Tunisia";
$country[] = "Turkey|Turkey";
$country[] = "Turkmenistan|Turkmenistan";
$country[] = "Turks And Caicos Islands|Turks And Caicos Islands";
$country[] = "Tuvalu|Tuvalu";
$country[] = "Uganda|Uganda";
$country[] = "Ukraine|Ukraine";
$country[] = "United Arab Emirates|United Arab Emirates";
$country[] = "United Kingdom|United Kingdom";
$country[] = "United States|United States";
$country[] = "Uruguay|Uruguay";
$country[] = "Uzbekistan|Uzbekistan";
$country[] = "Vanuatu|Vanuatu";
$country[] = "Vatican City State|Vatican City State";
$country[] = "Venezuela|Venezuela";
$country[] = "Viet Nam|Viet Nam";
$country[] = "Virgin Islands, British|Virgin Islands, British";
$country[] = "Virgin Islands, U.S.|Virgin Islands, U.S.";
$country[] = "Wallis And Futuna|Wallis And Futuna";
$country[] = "Western Sahara|Western Sahara";
$country[] = "Yemen|Yemen";
$country[] = "Zambian|Zambian";
$country[] = "Zimbabwe|Zimbabwe";

I used two values in the array, one for the option value and one for the option name. This way you could use country codes if you wanted to for storing data from the form. So the array and the table both have 233 entries. Now lets run them side by side:


$db_start = microtime(true);
echo "Start Time of Database Code: $db_start
"; $dbc = mysql_connect('host','user','pw'); mysql_select_db('demo'); $name = 'country_menu'; $class = 'test'; $selected = ''; //create menu from db $menu = "\n"; echo $menu; $db_end = microtime(true); $db_time = $db_end - $db_start; echo "
End of Database Code: $db_end&
"; echo "Execution Time: $db_time

"; $array_start = microtime(true); echo "Start of Array Code: $array_start
"; // Country Array $country[] = "Afghanistan|Afghanistan"; //code removed $country[] = "Zimbabwe|Zimbabwe"; //create menu from array $select_menu = ""; echo $select_menu; $array_end = microtime(true); $array_time = $array_end - $array_start; echo "<br>End of Array Code: $array_end
"; echo "Execution Time: $array_time"; $ratio = round($db_time / $array_time,2); echo "

Array is " . $ratio . "X Faster
";

And the results, from running the script numerous times, the array is 5-40x faster than the database. Both take relatively the same time to add a new record to if you need to make any edits. For static data like this and provinces/states I would definitely recommend using an array over pulling data from the database. If you need to store more info than a name and be able to sort it then a table is the way to go.

Run my test yourself and see how it turns out.

Continue Reading