Mysql – How to auto-refresh/re-run a query every few seconds

MySQLmysql-workbench

I have a script running against a database with no GUI. This simple query lets me see its progress:

select
    *,
    (select count(*) from domains) as count,
    (select 456976-count(*) from domains) as remaining
from domains
order by domain desc
limit 0, 1;

Using MySQL Workbench, how can I automatically refresh/re-run this query every few seconds?

I tried Googling, but didn't come up with anything relevant.

^{Aside: the magic number 456,976 is 26^4; it has to do with the script.}

Best Answer

First, create a Stored Procedure that calls the query and sleeps every 10 seconds

use mydatabase
DELIMITER $$
DROP PROCEDURE IF EXISTS `MyDataStatus` $$
CREATE PROCEDURE `MyDataStatus` ()
BEGIN
    SET @SecondsToPause = 10;
    SELECT COUNT(*) INTO @CurrentCount FROM domains;
    SET @Remaining = 456976 - @CurrentCount;
    WHILE @Remaining > 0 DO
        SELECT *,@CurrentCount as `count`,@CurrentCount as `remaining`
        FROM domains ORDER BY domain DESC LIMIT 0, 1;
        SELECT SLEEP(@SecondsToPause) INTO @SleepValue;
        SELECT COUNT(*) INTO @CurrentCount FROM domains;
        SET @Remaining = 456976 - @CurrentCount;
    END $$
DELIMITER ;

Now, call the Stored Procedure from DOS Command Line

set MYSQL_USER=root
set MYSQL_PASS=password
set MYSQL_MYDB=mydatabase
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS% -D%MYSQL_MYDB%
mysql %MYSQL_CONN% -ANe"CALL MyDataStatus()"

or you could login to mysql

mysql %MYSQL_CONN%

then call the query at the MySQL prompt

mysql> CALL MyDataStatus();

Give it a Try !!!

Caveat

Please notice I call the COUNT once and subtract it from 456976 to speed up the count a little.

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

First, each time you UPDATE the status column, you are having to update the index as well (source). Evaluate your indexing to see if you really need the index on the status column. My guess is no, since it has an extremely low cardinality and MySQL probably won't use it anyway.

If you ignore me and think you do need it, follow the advice in the article to drop the index before your loop and re-add it after you're done.

Here are some other things you might do if that doesn't help:

You are taking all the columns from the data but only using number. Don't do a SELECT *, but instead a SELECT number. That won't help your writes, but it is a good performance practice. Only select the columns you're using.
Your number index isn't getting used at all. This means it is not unique enough to be useful for updating. (Slight tangent: how many rows does a single UPDATE affect?) I would drop it, or at least add it to process index.
It looks like process is unique enough for MySQL to whittle the amount of rows down to 16k, instead of 1 million. In light of this, I would add AND process=x to your update statement (I'm assuming you know process from the original SELECT statement):
```
-- FAILED--
UPDATE data SET status = 2, error='$error' WHERE process=X AND number = $data['number']

-- SUCCESS --
UPDATE data SET status = 1 WHERE process=X AND number = $data['number']
```

A hint about unnecessary indexes in InnoDB. InnoDB is using a hidden 'primary key' (since you don't have one defined) and is using that when it writes the indexes. So for each Index you're using, you add the size of the index + the size of the hidden primary key to the data file. If you're not using the index (or MySQL can't use it), you are wasting space and adding overhead each time you insert a new number (same for status, as discussed earlier)

Mysql – Limit WHERE to MAX() & COUNT()

Here is your original query from the question

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM TAB_EVENT e 
LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id 
WHERE ( m.`read` IS NULL OR m.`read` = 0) 
GROUP BY e.id 
ORDER BY m.datetime DESC, e.id ASC 
LIMIT 10;

Maybe try refactoring the query in such a way that in executes in this sequence

only collect necessary columns from TAB_MESSAGE
apply LIMIT 10 against the collected rows from TAB_MESSAGE
run the JOIN
apply the MAX() and COUNT() last

Here is what I am proposing

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
LEFT JOIN TAB_EVENT e 
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

Give it a Try !!!

UPDATE 2012-02-21 17:06 EDT

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

@Sebastian, I put the query back in the original join order. Please try this as well !!!

UPDATE 2012-02-21 17:11 EDT

Make sure the datetime field is indexed

ALTER TABLE TAB_MESSAGE ADD INDEX read_datetime_ndx (read,datetime);

Best Answer

Give it a Try !!!

Caveat

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

Mysql – Limit WHERE to MAX() & COUNT()

UPDATE 2012-02-21 17:06 EDT

UPDATE 2012-02-21 17:11 EDT

Related Question