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

[milesdriven]

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.

[Old Pedant]

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.)

[milesdriven]

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?

[Old Pedant]

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:

CREATE INDEX nameOfYourChoice ON tablename(columnname);

Variations on that:

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

CREATE UNIQUE INDEX user_email_idx ON users(email);

That is, all email addresses ought to be unique.

Or maybe

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.

[XmisterIS]

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:

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.
);

[guelphdad]

WHEN is a reserved word, call it appointment_time or something similar.

[Old Pedant]

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.