When creating the temp table, add another column, isn2, that is SUBSTR(ISN, 2). Add INDEX(isn2, type) Change the query to... isn2 = '" & isn & "' ORDER BY type LIMIT 1
It that does not work, please provide clearer details, including SHOW CREATE TABLE SHOW TABLE STATUS EXPLAIN SELECT (with substitutions filled in) > -----Original Message----- > From: Andrés Tello [mailto:mr.crip...@gmail.com] > Sent: Tuesday, October 09, 2012 7:04 AM > To: Adrián Espinosa Moreno > Cc: mysql@lists.mysql.com > Subject: Re: Slow queries / inserts InnoDB > > 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 < > aespinosamor...@gmail.com> 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. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
