0 Cart
Added to Cart
    You have items in your cart
    You have 1 item in your cart
    Total
    Check Out Continue Shopping

    Frequently Asked Questions could include your suggestions.

    Q. Command line/Command prompt usage termination? After completing command line/prompt activities, how do I close it?

    Linux ctrl+c to release resources and take your PID off the top/htop list of activities

    Windows type 'exit' at the command prompt and press enter, or close your Window you are working in (top right)
    there are likely other ways, if you know an easy one, please email suggestions
    to info@mysqlservertuning.com with Subject of 'faq suggestion'.
    Hard Shutdown is another way, but please avoid this solution ).

    Q. Computer to computer connectivity options?
    Used by Back To Basics, Inc. at some point in the past

    join.me
    Products for 3/18 Windows, Mac 03/2018
    When will linux be supported by join.me?
    Check their web site, bottom of page. Select Knowledge Base, search for linux.

    LogMeIn.com
    Products for: 3/18 Windows to be researched

    SKYPE.com
    Products for: 3/18 Windows, Linux, Android, Mac, IOS, Web, Xbox One

    Teamviewer.com
    Products for: 3/18 Windows to be researched

    Q. General log use

    SET GLOBAL general_log=1;    to turn ON
    in 15 seconds (or whatever time you want to end the logging)
    SET GLOBAL general_log=0;    to turn OFF

    On a busy system, 1 minute of General Log will be a very large file.

    FAILURE to turn OFF your general log will result in VERY BAD NEWS.
    For additional information refer to your version's reference manual.

    SELECT @@general_log_file;  to view the name of your general query log file.

    Q. OPTIMIZE - Why should I use OPTIMIZE?
    The same reason someone needs to take out the trash, occasionally.

    When rows are added to tables, indexes need to have somewhere to live.
    This process involves 'splitting pages' and keeping up with where they are.
    If your data table is added to or changed, you may have a page split and
    be unaware of what has been done for you behind the scenes.
    OPTIMIZE will take care of getting your data back together for improved performance. Table will be LOCKED during the process.

     Q. Are there faster alternatives?
    ALTER TABLE tbl_name ENGINE=INNODB
    is a real good candidate.

    Likely, will someone provide timing comparisons, please.

    Q. Why is MySQL slow?
    Poorly tuned instance (or never tuned) - our quarterly services will help.
    Table(s) with no PRIMARY KEY.
    A) PRIMARY KEY column attributes MUST include UNIQUE and NOT NULL attribute for best performing tables.

    Details to check on a reasonably active table, (maybe a table showing up in Slow Query Log frequently)

    SHOW INDEX FROM tbl_name;
     In first detail line Non_unique column = 0; UNIQUE key values are REQUIRED
        if Key_name column = PRIMARY
        AND the Column_name = columnname you would choose
        this aspect of your table can not be improved.

    If you have more than 5 filenames for indexes, you are likely 'over indexed' and
    this will cause additional CPU use and extended time to be used for
    DELETE INSERT MERGE and UPDATE operations. Please review available tutorials/books on database index design and maintenance requirements for the many reasons to avoid a table with more than 5 indexes. We have seen 60 indexes many times on one table and yes, the workload is more significant than expected. And then you have the space requirements as well.

    As you review the index list, if you see a
    Column_name value that you can verify is NEVER used in a WHERE, JOIN, GROUP BY (there could be more) AND on that line, the Seq_in_index is 1, you have an UNUSED index, that will only waste CPU and space on your storage media. Carefully consider eliminating this one index - remembering, it could be used for annual or quarterly reporting.

    SHOW CREATE TABLE tbl_name;

    Q. Is the engine displayed InnoDB?
    No, go to some other technical resource,

    unless you want to talk about migration to INNODB.


    Q. Does SCT have the PRIMARY key listed that YOU would choose?
    If not, all reads are inhibited from performing well.
    Behind the scenes MySQL must have a PRIMARY key to minimize system activity, so they just create one the best the software can.


    Q. Do you know the NATURAL key for the table?
    If not, think about a filing cabinet, how would you find your data?
    Is the candidatecolumn UNIQUE (if it is listed as PRIMARY, the column is assumed to be UNIQUE in InnoDB) and column attribute of NOT NULL? This COULD be your PRIMARY key.

    To VERIFY the column is unique, 
    SELECT candidatecolumn, COUNT(*) FROM tbl_name GROUP BY candidatecolumn HAVING COUNT(*) > 1 LIMIT 20;
        the LIMIT is to conserve your time, only - there could be thousands.
        if anything is listed, they are your non-unique values, with count for the specific value.
    If anything is listed, this can NOT be your PRIMARY KEY, stop reading now for this purpose.

    To VERIFY every row is NOT NULL in the column, 
    SELECT COUNT(candidatecolumn) FROM tbl_name WHERE candidatecolumn = NULL;
        result must be ZERO for you to ALTER TABLE column for NOT NULL
    SELECT COUNT(candidatecolumn) FROM tbl_name WHERE candidatecolumn = NOT NULL;

    You now are aware of how many rows were counted for this table.
    SHOW INDEX FROM tbl_name; to get a row count from MySQL's perspective
    If it is not the same number as in the count of NOT NULL, Houston we have a problem AND you can not proceed with managing this table to get a real PRIMARY key. For documentation of current index structure and number of rows available from the OPTIMIZER's perspective.

    Q. How do I make the table perform better?
    After practicing in your own test/dev environment,
    GET your own tbl_name BACKUP you know HOW to RESTORE ----- and practice, if not, you could be sorry.

    ALTER candidatecolumn to be UNIQUE and NOT NULL
    ALTER candidate column to be PRIMARY

    Q. So, I have to do all these things?
    No, you can just leave it alone and it will run as is, just slower than if these details were followed to enable higher performance and your read data counts will be lowered in SHOW GLOBAL STATUS reporting.

    Another side effect of completing this is the likely reduction in size of each ndx file you have defined for this table.

    Last details:
    SHOW INDEX FROM tbl_name; to get current statistics and cardinality from MySQL's perspective for comparison to SHOW INDEX results prior to this effort.