Mysql在xampp上意外停止

Question

我有生产软件，每个表超过50K行。它在插入和值检索方面执行速度很慢。表已经建立了索引。我的Ram是14 My，使用Xenon处理器。

如果我从my.ini文件中增加innodb_buffer_poll_size 3G或更多，那么mysql会停止unexpectedly.Whats这个问题。我现在能做什么？

Answer 1

请勿使用mysql_*接口！

        SELECT  Line
            FROM  sewing_effi
            WHERE  DT='$dt'
            ORDER BY  Line ASC"

DT的数据类型是什么？(请提供SHOW CREATE TABLE)

假设DT是一个DATE，那么你需要INDEX(DT, Line)来获得额外的效率。

为此：

        SELECT  buyer.Buyer,ordr.ordr,ordr.Ship_Date,colour.Colour,sewing_effi.OP,
                sewing_effi.HEL,sewing_effi.Target,sewing_effi.TWH,sewing_effi.Line,
                sid_cutting_inventory.DT,
                SUM(cutting_inventory.qc_pass) AS Input,
                SUM(sewing_output.QC_Pass) AS Toutput, SUM(sewing_output.Recieved) AS Recv
            FROM  cutting_inventory
            INNER JOIN  sid_cutting_inventory
                ON cutting_inventory.SID=sid_cutting_inventory.SID
            LEFT JOIN  buyer  ON cutting_inventory.Buyer=buyer.CBuyer
            LEFT JOIN  ordr  ON cutting_inventory.Buyer=ordr.CBuyer
              AND  cutting_inventory.Ordr=ordr.COrdr
            INNER JOIN  colour  ON cutting_inventory.Buyer=colour.CBuyer
              AND  cutting_inventory.Ordr=colour.COrdr
              AND  cutting_inventory.Colour=colour.CColour
            LEFT JOIN  sewing_output  ON sewing_output.Barcode=cutting_inventory.Barcode
            JOIN  sewing_effi  ON sewing_effi.Line=sid_cutting_inventory.Line
            WHERE  sewing_effi.Line ='$li'
              AND  sewing_effi.DT='$dt'
              AND  ordr.Ship_Status='0'
              AND  sid_cutting_inventory.Challan_Close_SS='0'
              AND  sid_cutting_inventory.DT NOT BETWEEN '2017-01-31  7:00:00'
                                                    AND '2018-03-31 23:00:00'
            GROUP BY  sewing_effi.Line, buyer.Buyer, ordr.ordr,colour.Colour
            ORDER BY  sewing_effi.Line"
                          )

我发现将LEFT JOIN和INNER JOIN混合在一起非常令人困惑。请提供EXPLAIN SELECT ...，我会进行一些猜测。

sewing_effi:  INDEX(Line, DT)  -- in either order
sid_cutting_inventory:  INDEX(Challan_Close_SS, DT) -- in this order

您是否安装了32位或64位版本的MySQL？

Answer 2

安装了32位版本的mysql.I。我附上了一些表的快照并解释了。我想你会很容易发现我的问题。Expalin cutting_inventory table sewing_effi table sewing_output table sid_cutting_inventory table

*Challan_Receive_SSD和Challan_Close_SSD列存储日期*Challan_Receive_SST和Challan_Close_SST列存储时间