WordPress Database Time Test: Arrays vs Booleans

While I was working on my plugin, Alliance Intranet, today I came to the item on my to-do list ‘re-write the settings and module toggle page’. I have been wondering lately if it would be faster to save and read multiple options, such as module toggles (if the module is active or not) as serialized data in the database, or if I should spit each module toggle out into its own option entry in the database. In order to figure this out were going to do some time tests.

Lets work with the module toggle, because thats the first one I am working on. The module toggle shows what modules are activated for the WordPress install.

currently I have all my data saved into an array. When un-seralized it looks like this:

This is what the option looks like in the database:

option_id option_name option_value autoload
135 mydn_module_toggle a:10:{s:30:”mydn_activate_branding_library”;s:1:”1″;s:39:”mydn_activate_internal_document_library”;s:1:”1″;s:28:”mydn_activate_login_redirect”;s:1:”1″;s:27:”mydn_activate_policy_manual”;s:1:”1″;s:31:”mydn_activate_procedure_library”;s:1:”1″;s:29:”mydn_activate_project_library”;s:1:”1″;s:33:”mydn_activate_publication_library”;s:1:”1″;s:33:”mydn_activate_translation_library”;s:1:”1″;s:27:”mydn_activate_user_mentions”;s:1:”1″;s:27:”mydn_activate_user_profiles”;s:1:”1″;} yes

So this got me thinking what if I stored the data in seperate rows for each toggle, in which case the option data in the database would look like this:

option_id option_name option_value autoload
150 mydn_toggle_brand 1 yes
151 mydn_toggle_docs 1 yes
152 mydn_toggle_login 1 yes
153 mydn_toggle_policies 1 yes
154 mydn_toggle_procedures 1 yes
155 mydn_toggle_projects 1 yes
156 mydn_toggle_publications 1 yes
157 mydn_toggle_translations 1 yes
158 mydn_toggle_mentions 1 yes
159 mydn_toggle_profiles 1 yes

I decided to write some PHP to see which one was faster, below is the PHP that I wrote and the results.

First lets run a test against the array. Because it takes fractions of a second to check this data I wrote and ran it 100,000 times. Below is the PHP I wrote to check this option.

Below are the results of the first test:

Description Test 1
Time Pre: 1431785315.59
Time Post: 1431785330.84
Execution Time: 15.2570419312 Seconds

Alright, now that we have a time for checking options stored in an array in the database, lets check an option that is not stored in an array.

Below are the results of the first and second test side by side:

Description Test 1 Test 2
Time Pre: 1431785315.59 1431792127.1
Time Post: 1431785330.84 1431792138.96
Execution Time: 15.2570419312 Seconds 11.8610730171 Seconds

That comes out to a difference of 3.3959689141 seconds over 100,000 iterations, coming to 0.000033959689141 seconds per itteration. Its not much of a gain, but it is enough that I will definatly consider switching for places where I am only checking one at a time. However, when checking all of them at once, I am still unsure if it is better in terms of bandwidth and processing to make 10 calls to the database and retrieve a bool every time, or to make 1 call to the database for an array and check the array with PHP.

Leave a Reply

Your email address will not be published. Required fields are marked *