Re: Performance by Selections
Posted by:
Greg Mundt
Date: March 04, 2019 10:14AM
Create Table Results:
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pos_ord_headers | CREATE TABLE `pos_ord_headers` (
`ORD_ID` varchar(20) COLLATE ascii_bin NOT NULL DEFAULT '',
`ORD_Date` date NOT NULL DEFAULT '0000-00-00',
`ORD_Time` time NOT NULL DEFAULT '00:00:00',
`ORD_Store` varchar(4) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_Register` varchar(15) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_Whse` char(2) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_User_ID` varchar(10) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_User_Name` varchar(30) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_Merch_Amt` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Disc_Amt` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Disc_Pcnt` float(9,4) NOT NULL DEFAULT '0.0000',
`ORD_Tax_Rate` float(9,4) NOT NULL DEFAULT '0.0000',
`ORD_Total_Tax` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Line_Disc` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Return_Amt` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_TaxExempt_ID` varchar(10) COLLATE ascii_bin NOT NULL DEFAULT '',
`ORD_Total_Tax2` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Total_Tax3` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Hour` tinyint(2) NOT NULL DEFAULT '0',
`ORD_Taxable_1` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Taxable_2` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Taxable_3` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_NonTax_1` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_NonTax_2` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_NonTax_3` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Cost` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Disc_ID` varchar(10) COLLATE ascii_bin NOT NULL,
`ORD_Disc_Desc` varchar(60) COLLATE ascii_bin NOT NULL,
`ORD_Mgr_ID` varchar(10) COLLATE ascii_bin NOT NULL,
`ORD_Mgr_Name` varchar(30) COLLATE ascii_bin NOT NULL,
`ORD_Total_Tax4` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Total_Tax5` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Taxable_4` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Taxable_5` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_NonTax_4` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_NonTax_5` float(15,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`ORD_ID`),
KEY `Store_Date` (`ORD_Store`,`ORD_Date`),
KEY `ORD_Date` (`ORD_Date`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin MAX_ROWS=1000000000 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pos_ord_lines | CREATE TABLE `pos_ord_lines` (
`ORD_ID` varchar(20) COLLATE ascii_bin NOT NULL DEFAULT '',
`ORD_Line` char(3) COLLATE ascii_bin NOT NULL DEFAULT '',
`ORD_Item` varchar(20) COLLATE ascii_bin NOT NULL DEFAULT '',
`ORD_Desc` varchar(30) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_Qty` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Price` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Taxable` char(1) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_IGrp_Status` varchar(5) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_Item_Class` varchar(30) CHARACTER SET ascii NOT NULL DEFAULT '',
`ORD_Disc_Amt` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_Disc_Pcnt` float(9,4) NOT NULL DEFAULT '0.0000',
`ORD_Sort_Item` varchar(20) COLLATE ascii_bin NOT NULL DEFAULT '',
`Ord_Return_Reason` char(4) COLLATE ascii_bin NOT NULL,
`ORD_BOGO_Discount_Code` smallint(6) NOT NULL DEFAULT '0',
`ORD_BOGO_Discount_Amt` float(15,4) NOT NULL DEFAULT '0.0000',
`ORD_COST` float(15,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`ORD_ID`,`ORD_Line`),
KEY `Items` (`ORD_Item`),
KEY `Item_Classes` (`ORD_Item_Class`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin MAX_ROWS=1000000000 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
I am not sure how to find if swapping is induced. MySQL is running on a RedHat Linux based system
The machine is not dedicated to MySQL and I am not certain how much is available.
Key buffer size:
+-------------------+
| @@key_buffer_size |
+-------------------+
| 8388608 |
+-------------------+
1 row in set (0.00 sec)