List:General Discussion« Previous MessageNext Message »
From:Cheng Xianming Date:October 10 2012 1:03am
Subject:Re: Slow queries / inserts InnoDB
View as plain text  
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.
>>

Thread
Slow queries / inserts InnoDBAdrián Espinosa Moreno8 Oct
  • Re: Slow queries / inserts InnoDBAndrés Tello9 Oct
    • RE: Slow queries / inserts InnoDBRick James9 Oct
    • Re: Slow queries / inserts InnoDBCheng Xianming10 Oct