From: Cheng Xianming Date: October 10 2012 1:03am Subject: Re: Slow queries / inserts InnoDB List-Archive: http://lists.mysql.com/mysql/228357 Message-Id: <5074C973.5090702@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Tello is right ! Moreno, and I sugest that: if you want query by "SUBSTR(ISN,2)" ,you should create a field named like "isnInfo", and save SUBSTR(ISN,2) to this filed when you insert . don't forget create a index on this field . ------------------------------------ 于 12-10-9 下午10:04, Andrés Tello 写道: > You are forcing mysql to do full table scans with the substr... > > Use explain to see that you aren't using any index. > > Avoid the use of substr in the where clause, by splitting your data, index > that field and do you query over that field. > > > > That is why your query is so slow. > > the slow insert, is due you S.O... > > > On Mon, Oct 8, 2012 at 2:59 AM, Adrián Espinosa Moreno < > aespinosamoreno@stripped> wrote: > >> Hi. >> >> >> >> I have developed my first application with VB.NET at work (junior >> developer) and basically it reads several thousands of lines from X number >> of files, and inserts them into a mysql database. >> >> >> >> The application consists of several steps: >> >> 1) Dump all lines with all fields into a temp table. >> >> a. This works fine. Optimal speed for the hardware we have. >> >> 2) Query temp table to obtain certain. I query by a unique ID. I >> insert all unique ID (isn field). If the ID matches my interests, I insert >> it into an ArrayList. >> >> a. Query: SELECT COUNT(SUBSTR(ISN,2)), SUBSTR(isn, 2) FROM >> SMS.TEMPDATADUMP WHERE error = 0 GROUP BY SUBSTR(ISN,2) HAVING >> COUNT(SUBSTR(ISN,2)) = 4 >> >> b. The isn is not unique per line, but per data (sms) >> >> c. Once I have all isn on an arraylist, I do the following query: >> >> i. >> SELECT >> select, my, fields,of,interest FROM SMS.TEMPDATADUMP WHERE SUBSTR(ISN, 2) >> = '" & isn & "' ORDER BY type LIMIT 1 >> >> d. To obtain some data. During the process I query around 10 times >> other table per ISN. >> >> e. Here is the problem. If I have a few files to process (around >> 3000-4000 lines in total, small array) this steps work fine, good speed. >> But If I have big files or a lot of files (more than 10000 lines in total, >> big array), this steps are incredibly slow. Queries and inserts are too >> slow. Meaning, one-two inserts per second, while the other case inserts are >> around 800 per second. >> >> >> >> Our hardware is not optimized for database server, but I don’t have other >> choice. It is mostly a desktop computer >> >> Intel core i5, windows 32 bits, 3GB RAM, one disk 500GB 7200rpm. >> >> >> >> I have tried some optimizations commented in mysqlperformance blog without >> success. >> >> Any way to optimize this? >> >> >> >> Thank you very much in advance. >> >> >> >> >> >> Adrián Espinosa Moreno. >>