Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    db keys and efficiency

    Hi,
    I am making a db using sqlite3 with python , but my question is pretty general I think even if you don't know this db system you can probably help me. I am trying to decide what the best key(s) are to use in a table. Here is what it might look like using the common tutorial analogy:

    Customer, OrderDate
    A, 2012-1-1
    A, 2012-1-2
    B, 2012-1-1
    B, 2012-1-2

    In my db there could be 50 customers but each customer could in extreme cases have 10000 OrderDates! My application will repeatedly be selecting all the orders from one customer between a date range.

    Is there any way that I can set up the primary keys so that this type of query will be more efficient? Customer + OrderDate will be unique, will there be any advantage of making these combined columns the primary key since I will only query for date ranges and not specific dates?

    My expectation is not that this will run in seconds but I would like to avoid taking 30 minutes if it could take 5...

    Thanks so much for your help!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Switch the order of the columns, use OrderDate then Customer to get the best of both worlds!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2012
    Posts
    2
    Switch the order of the columns, use OrderDate then Customer to get the best of both worlds!
    Thanks Pat! Could you explain why this would help?

Tags for this Thread

Posting Permissions

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