List:General Discussion« Previous MessageNext Message »
From:Rick James Date:October 9 2012 5:49pm
Subject:RE: Slow queries / inserts InnoDB
View as plain text  
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.criptos@stripped]
> Sent: Tuesday, October 09, 2012 7:04 AM
> To: Adrián Espinosa Moreno
> Cc: mysql@stripped
> 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 <
> 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