SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2003
    Location
    l'Europe
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    inactive records

    Hi,

    I use in my php application queries with unions and joins... In each table are a few ten thousands of records and it is getting a bit slower on some requests (the indexes I use are OK).
    Because most new data is only used for maximum 3 months, I thought on getting rid of the 'inactive' records, but not deleting them (search purposes).
    Does anyone has any experience with this?
    I had two ideas:
    1. copy the inactive rows to 'archive tables' where I can still search data, and I could transfer a row back to the active table if it should be needed again.
    2. I add an extra field to each table: 0 = inactive, 1 = active; in queries I could make a difference with that field included, avoiding the inactive ones.

    In both cases, I need to rewrite my php-scripts, but idea n2 looks the easiest one. Could this help me to keep the querying fast?

    Thank you for any suggestion!

    Ann

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    an inactive flag probably won't make the queries run faster

    even if the indexes are OK (what does that mean?), you need to EXPLAIN the various queries to ensure that each query is using the most appropriate index

    separate archive tables will probably work, but any query that needs archive data along with current data will be even slower
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •