Professional Web Applications Themes

MySQL table speed access and design issues - MySQL

Hello, I am writing some code where the database information is run heavy for a short period of time, and then is "archived", but still in use (just not at the same capacity). So, for example, I will have a set of data (about 2500 records) that will be constantly in use (select statements) for about two months. Currently, that data remains in one table, I add the next set of records, and the process continues for the next couple months before I get the next set of data. The data is constantly growing, but only certain records are being ...

  1. #1

    Default MySQL table speed access and design issues

    Hello,

    I am writing some code where the database information is run heavy for
    a short period of time, and then is "archived", but still in use (just
    not at the same capacity). So, for example, I will have a set of data
    (about 2500 records) that will be constantly in use (select
    statements) for about two months. Currently, that data remains in one
    table, I add the next set of records, and the process continues for
    the next couple months before I get the next set of data. The data is
    constantly growing, but only certain records are being used
    constantly, where the others are used a few times at most.

    So it got me thinking... Would it make sense to create two tables to
    speed up the process? One table would be the currently active data,
    and the other would be the archived data. Are there any drawbacks to
    this approach from a performance aspect? Would it make a substantial
    difference in performance as the data gets up in numbers? Is there
    anything I might be missing in considering this approach?

    Any comments, critiques, or suggestions are greatly appreciated! Let
    me know if I'm missing any details that might help...

    Nino Skilj

    nino9stars@yahoo.com Guest

  2. #2

    Default Re: MySQL table speed access and design issues

    >I am writing some code where the database information is run heavy for 
     

    Queries that need data from *BOTH* tables (or from either table)
    are going to get messy. (For example, year-to-date queries). How
    often do you do that?
     

    Always remember that you can do anything infinitely fast if the
    answer is not required to be correct.

    Gordon Guest

  3. #3

    Default Re: MySQL table speed access and design issues

    On Apr 16, 3:26 pm, org (Gordon Burditt) wrote: 
    >
    > Queries that need data from *BOTH* tables (or from either table)
    > are going to get messy. (For example, year-to-date queries). How
    > often do you do that?[/ref]

    Well, if I were to take this approach of having two tables, they would
    be fairly separate. The archived data would only be used in reference
    to a session variable I would create to denote time. So, when the date/
    timeframe for certain data is past, it would automatically reference
    the archived table. Likewise, if the date/timeframe is current, it
    would access the data from the current table. I'm not worried so much
    about the implementation or data, as I feel it wouldn't run much
    differently (design wise) if I were to separate the tables. I'm more
    curious to know if this will actually improve the performance of my
    application, or it doesn't really matter. Of course it's easier to
    keep everything in one table, but if it makes a difference, I'd be
    willing to go through the hassle of changing my design.

     
    >
    > Always remember that you can do anything infinitely fast if the
    > answer is not required to be correct.[/ref]

    Unfortunately the answer has to be correct. :)

    Nino

    nino9stars@yahoo.com Guest

  4. #4

    Default Re: MySQL table speed access and design issues

    Hi,
     

    What you could do is creating two tables, and an additional MERGE
    table for queries that need to get data from both the archived and current
    data.


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  5. #5

    Default Re: MySQL table speed access and design issues


    <com> schreef in bericht
    news:googlegroups.com... 

    You could consider to create from the table data a subset in a memory table
    and use that for your queries.
    That would leave the table free for updates and speed up the queries.


    Hans Guest

  6. #6

    Default Re: MySQL table speed access and design issues

     
     
    ..... 

    It depends of what is your data. 2500 high quality photos would be a
    problem, but 2500 words wouldn't.

    These problems are considered by engineeries
    (my-spelling-went-wrong-somewhere?) that make database softwares and it is
    solved in the way that it works at its best.

    Duz Guest

Similar Threads

  1. MS Access with Mysql link table connection failed
    By pelhughes in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 3rd, 11:13 PM
  2. MS Access with MySQL - Speed Issues
    By Sam in forum MySQL
    Replies: 4
    Last Post: September 29th, 12:30 PM
  3. Cfdoent Speed issues
    By StokeyTCI in forum Coldfusion - Advanced Techniques
    Replies: 8
    Last Post: April 4th, 03:04 PM
  4. Lost MySql Table Access
    By r.mcinteer in forum Dreamweaver AppDev
    Replies: 2
    Last Post: March 29th, 10:33 AM
  5. Balancing speed against OO design - optimization issues with PHP/MySQL
    By Jeffrey Silverman in forum PHP Development
    Replies: 3
    Last Post: August 12th, 08:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139