[SOLVED] Understanding the result of `SELECT * FROM INFORMATION

Understanding the result of `SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST`

I want to make sure my php code and my website does not use permanent connections.

I use the sql query below to check but I don't quite understand the output,

$sql = "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST";
$items = $connection -> fetch_all($sql);
print_r($items);

Below is the output,

Array
(
    [0] => Array
        (
            [ID] => 43
            [USER] => root
            [HOST] => localhost:52246
            [DB] => xxx_2010
            [COMMAND] => Query
            [TIME] => 0
            [STATE] => executing
            [INFO] => SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
        )

    [1] => Array
        (
            [ID] => 38
            [USER] => root
            [HOST] => localhost:52140
            [DB] => 
            [COMMAND] => Sleep
            [TIME] => 423
            [STATE] => 
            [INFO] => 
        )

)

when I clicked on another page,

Array
(
    [0] => Array
        (
            [ID] => 44
            [USER] => root
            [HOST] => localhost:52301
            [DB] => xxx_2010
            [COMMAND] => Query
            [TIME] => 0
            [STATE] => executing
            [INFO] => SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
        )

    [1] => Array
        (
            [ID] => 38
            [USER] => root
            [HOST] => localhost:52140
            [DB] => 
            [COMMAND] => Sleep
            [TIME] => 1026
            [STATE] => 
            [INFO] => 
        )

)

Why is it always output two arrays? What does it mean by Query/Sleep in the [COMMAND]? in the [STATE] it says executing in the first array then became empty in the second array - does it mean that the db connection disconnects once the page has been sent.

Also, [TIME] => 0 and [TIME] => 423 - what does it mean?

Why there are a few keys display different values such as [HOST] => localhost:52246, [HOST] => localhost:52140, [ID] => 43, [ID] => 38, etc

Sorry I might have just asked some dumb questions...

Thanks.

EDIT:

Now I have this in the output after closing phpMyadmin on my browser,

Array
(
    [0] => Array
        (
            [ID] => 59
            [USER] => root
            [HOST] => localhost:53195
            [DB] => xxx_2010
            [COMMAND] => Query
            [TIME] => 0
            [STATE] => executing
            [INFO] => SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
        )

)

So whenever I click around on my site, it always returns [STATE] => executing - does it mean that my site is on permanent connections?

Solution

You have one connection open from the root user that has been idle for quite some time (the ID is the same, and the sleep time is quite high). Something has it open and hasn't closed it (Perhaps an unclosed connection from PHPMyAdmin, or something like that)...

Your application isn't using the same connection (you can see from the ID column). As far as the other connection you can try KILLing it and seeing if it returns. If it doesn't, I wouldn't worry too much about it.

One other thing, don't use root to login to a MySQL server. Create a user with limited permissions, and grant the PROCESS permission. But don't ever connect as a full-priveleged user...