Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
A hard issue to address with so little information, but I can offer a few tips that may help

* if you can, create a 2nd table in your database that contains just your key record (i am assuming you have one) and the two fields you're basing your query on. You can even create this on the fly as a temporary table which is never written to disk, and then query it. Use your array of key values to fetch all 200 fields one record at a time based on your key value, which should be indexed.

* Don't bother creating an index on mw_export, that will probably slow down your table's performance, especially your write performance (because on write/update the table updates all affected indexes). Indexes don't help on columns that just contain one or two possible values... like yes/no.

* You didn't specify, but perhaps you're loading a big data file from another source, like a CSV into a table before processing the records, and that's why the table design is so horrid? If this is the case, don't create the indexes on the table until after you've loaded the data into it. This way, it just generates it's index once, not every time you insert a record into the table.

* When you *have* to build a table like this, it gets big. When a table is this size, with this many fields and records, very small optimizations make a large differnce over time. Review all of your column types and make sure you are being as efficient as possible. Easy things to check are field sizes compared to data sizes, setting NOT NULL on columns that will not contain null values saves you storage and processing each time that value is accessed. Using CHAR instead of VARCHAR saves you a byte of storage and requires less processing by the server to pull that value. CHAR vs VARCHAR depends a lot on your data, and the engine you're using on that table. You can pick up a good comprehensive MySQL reference like "MySQL by Paul DuBois" from O'Reilly ISBN0-7357-0921-1 (available online if you're a member of Find as many ways to optimize your columns and data as possible.

* As you process your records one at a time, be sure to retain as little data as possible from the rest of the records in memory. If you're taking each record, storing it's hash reference for later access and moving on, you're forcing your script to keep the entire record set in memory. You want to load one record at a time and when you're done with it, make sure you're letting the garbage collector ditch the data instead of holding onto it by keeping a reference to it's hash defined somewhere.

good luck!

In reply to Re: DBI speed up needed on MySQL by Perimus
in thread DBI speed up needed on MySQL by jacques

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others about the Monastery: (3)
    As of 2021-01-18 23:59 GMT
    Find Nodes?
      Voting Booth?