Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Dec 2011
    Posts
    186
    Thanks
    0
    Thanked 1 Time in 1 Post

    How do I write a query that joins/searches 90 small tables?

    I have php code that uses a 3 separate for loops to create the days of any three month period. I have another set of 3 separate for loops that create one mysql table for each day of this three month time period. Each table holds appointment information for one day of this 3 month period.

    I have a registration table that is populated by a registration form. The customer's user id is the primary key of this table.

    The appointment time column is the primary key of the "daily" tables, while the user id is the foreign key that links each daily table to the registration table.


    I want to write one query to search for appointments scheduled in this 3 month time period. This query would need to join about 90 small tables together (created by the January, Feb and March for loops - for example) to search for appointment times that customers scheduled (inserted into the table), anytime within this 3 month period.

    I've been studying queries, and most are focused on joining 1, 2 or 3 tables. I want to write a query that joins 30, 60 or 90 small tables to search for 1, 2 or 3 months worth of appointments.

    How do I write a query that joins all these tables? Thank you.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Simple answer: Get rid of the 90 tables. Consolidate them into one (or at least a few).

    It sounds to me like you have one table per day. Oh, yes, I see now. You say that. TERRIBLE database design. Unnecessarily complicates things for no benefit I can see.

    I assume you have the tables named by date? e.g., "AppointmentsFor3April2012" or something along those lines?

    Instead, have *ONE* table named "Appointments". And add a column named "appointmentDate". Assuming you index that column, your performance will be just as good when you are looking for a single date and much much better for anything involving multiple dates.

    ********

    Reading your post again, I think even better would be to simply change your current "appointmentTime" column in the daily tables into a single "appointmentDateTime" column. You can then leave it as your primary key.

    If you ever need to extract only the date from that column, you use DATE(appointmentDateTime) and you can extract only the time using TIME(appointmentDateTime).

    I *assume* your current column is a DATETIME column, yes? So you wouldn't even need to change the data type.

    I am begging you to change your design now, before you go any further down the ugly path you have started.

    Are you worried about the table size?? MySQL quite handily works with tables of several million rows.

    I have been working with one that has 12 million rows. The table has several indexes, but the most used one if phone number. It's not even the primary key, as there *are* duplicate phone numbers in the table. And yet I can search for any phone number and retrieve it in a few milliseconds. (In MySQL command line mode, the query registers as 0.000 seconds ... so less than 50 milliseconds, for sure.)

    Even if your appointments are only 10 minute apart and are spread across a 12 hour day, that's only 72 appointments, max, per day. Times, say, 4 years of data with appointments 5 days a week? Roughly 1000 days, so 72,000 records? That is *NOISE* to MySQL. I don't think I have any tables, except a few "lookup" tables (e.g., state abbreviations to state names) that are close to that small.

    And I'm serving up roughly 5 queries per second, on average. With millions and millions of records. At least 6 tables with over a million, often joining together 2, 3, or 4 of the tables. (Well, more than that, including the tiny lookup tables.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Dec 2011
    Posts
    186
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks very much for your advice - and the humor. You had me laughing, and glad I posted the question. It's always good to hear from someone with more experience than me.

    I'll carefully design one table so I can simplify the queries. By the way, how do I index a column?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Well, if it's the primary key, it's automatically indexed (unique index, in fact).

    To index other columns, you an either do it when you create the table [if you use a tool to create the table--such as myphpadmin--it probably has an option to make this easy] or you can use a simple SQL command:
    Code:
    CREATE INDEX nameOfYourChoice ON tablename(columnname);
    Variations on that:
    Code:
    CREATE UNIQUE INDEX nameOfYourChoice ON tablename(column1,column2,column3);
    UNIQUE says there can only be one of each value in the index.

    You can specify multiple columns to make up an index, if you wish.

    A reasonable index might be
    Code:
    CREATE UNIQUE INDEX user_email_idx ON users(email);
    That is, all email addresses ought to be unique.

    Or maybe
    Code:
    CREATE UNIQUE INDEX user_appointment ON appointments(userid, appointmentdate);
    If you didn't want to allow more than one appointment per user per day. Or remove the UNIQUE to still have the index but allow multiple appointments per user per day.

    There are other variations. Check the MySQL manual for "CREATE INDEX" for more options.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts
    miles - I'm with OldP on this one, there's nothing that man (or woman?) doesn't know!

    Off the top of my head, I'd create an appointments table like so:

    Code:
    create table if not exists appointments (
      id int(32) unsigned not null primary key auto_increment,  -- Unique index for each row.
      when datetime not null,  -- when the appointment occurs.
      title char(40),  -- A 40-character (max) title for the appointment (so you could perhaps display a list of hyperlinks to each appointment).
      about text -- More info about the appointment.
    );
    Last edited by XmisterIS; 03-30-2012 at 10:51 AM.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    WHEN is a reserved word, call it appointment_time or something similar.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    I don't agree with XmisterIS's choice of primary key.

    More accurately, I don't think it is the right answer in this particular case.

    Miles stated he was using [b]appointmentTime[b/] for the primary key in his daily tables. Meaning he only allows one appointment at each time. Given that, then making appointmentDateTime the primary key in an all-days appointments table is all that is needed.

    Don't add in auto_increment primary keys if they aren't needed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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