Best way to run this query on an SQL Database

Question

I have a website that I host with GoDaddy. At the moment if I want to run an SQL Query on the database I'm using GoDaddy's default phpMyAdmin interface to interact with the database (all done through the browser).

I have a table with 32,000 records. One of the items in the table contains a JSON string that looks something like this:

{
"activity": {
    "section1": {
        "item1": {
            "itemName": { 
                "name": "myName", 
                "property1": false, 
                "property2": false 
             }
         }
     },
     "section2":{ 
          "item1": false
     }
 }
}

Overtime I may want to update this JSON string (e.g. if the schema is updated and I want to add a section3 there. If I try to do this now (even if the new string is hardcoded and is the same for each of the 32000 records in the table, the query just times out. I suspect 32000 is too many records for this operation.

I tried running a query through PhpMyAdmin's SQL Query tab - that failed, it got through to about half way and then it timed out.

My question is: what is the best to work with the database? Is there a more efficient way to run queries then through GoDaddy's default phpMyAdmin interface?

There could be a few issues here but here are some things that you can do to figure out the problem. 1) A bad query, which if that's the case checkout this answer on stack overflow http://stackoverflow.com/questions/10137430/phpmyadmin-freezes-on-query-db-too-big 2) Another might be that the instance hosted by GoDaddy is under resourced for your DB. Check with GoDaddy to see how much resources your instance is allowed, you may need to move to a larger instance. 3) PhpMyAdmin maybe timing out in the webapp or the browser, try connecting to your MySQL DB using a thick client like MySQL Work — ImaginateWayne, Aug 10 '14 at 13:35

score 0 · Answer 1 · edited May 23 '17 at 12:11

I don't know which plan you use at GoDaddy, but you can enable remote access on all paid plans. Then, you can connect to your database using MySQL Workbench tool. I think that's better than PHPMyAdmin.

Another solution is execute the query using PHP (and maybe split up in multiple queries). You can host a PHP script directly on your GoDaddy server.

Anyway, storing JSON files as part of a full-text field is not a good idea. You may read few articles about database normalization. See also this other question.

Best way to run this query on an SQL Database

1 Answers1