500Mb is a meaningless measurement if you want to get a feel whether your database has many records to search through. It's meaningful when determining disk space need, or when doing backups, but not when you want to indicate you have a lot to search through. A 500 Mb database in a radiology lab probably means it only has one row - with a small image stored in it.

The number of rows a query might consider, now, that's an important measurement. The size of a row, both in columns (used in the query) and the total size in bytes are important as well, but much less so.

Having said that, 500 Mb is tiny by modern standards. Most desktops, and even many laptops will be able to keep almost the entire database in core memory - if you have a dedicated machine for your database (and you should), put in 1 Gb of RAM, and you'll be sure you have the entire database in memory.

But even if you have that, your approach can still be "slow". Whether or not that is significantly improveable depends almost entirely on your database structure (tables, indices), and the queries performed. If the queries could be almost anything, there will be many queries that will not be able to make use of the given indices, resulting to table scans. And even with the entire database in core memory, having to do many table scans will slow down things.

But as others said, this is mostly a database question. Consult your local database administrator/guru.

In reply to Re: How to improve MYSQL search performance of perl? by Anonymous Monk
in thread How to improve MYSQL search performance of perl? by nan

Title:
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, details, 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, summary, 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.