I recently converted my app to mySQL from MS Access. It has improved performance in all areas except one.
I have a many-to-many relantionship between two tables. The "middle" table has 300,000+ recs. When I join the three table in mySQL its incredibly slow (more than two minites) but when i run the same query in MS Access it takes aabout 10 secs.

I have setup the indexes on the tables, but I don't have relationships like the ones I had in Access.

Have I made a mistake by moving to mySQL ? I'm really surprised how poorly it did.


Can any one give some pointers ? I'd be happy to give moe details if it helps.


Thanks in advance

no you have not made a mistake by going from access to mysql

the relationships that access supports (and mysql does not) deal only with relational integrity (primary and foreign key consistency)

have you read 7.4.3 How MySQL Uses Indexes ?

can you show the three-way join query that runs slowly?

I have read the documentation on indexes. I think I'm useing them correctly, although I'm still on a learning curve. any way...


Here is a simplified verision of my select statement.

SELECT Horses.H_Name, Performances.FinPos, Races.Title
FROM (Races INNER JOIN Performances ON Races.R_ID = Performances.R_ID) INNER JOIN Horses ON Performances.H_ID = Horses.H_ID;

It is a m-m relationshhip. a horse can be in many races and a race can have many horses.

In my real statement i have a where part that uses two fields from Races, both are indexed seperatly.

approx. the horses table has about 40,000 recs, races about 36,000 and performances has 360,000.

I did an Expain on the query (sorry i cant show it to you, I'm in college at the moment), and it says that it scans 5000 recs from races, all horses and all from performances. This means (i think) that it has to do
5000 * 40000 * 360,000 comparisions. Am I right here ?

How the hell did Access do it so fast?

and you have indexes on which fields?

Can you post the output of the EXPLAIN as well as a DESCRIBE of the involved tables and a SHOW INDEXES for those tables?

I'll post the EXPLAINS and others tonight.


I have indexs on all the field in the joins. Races.R_ID,Preformances.P_ID,Horses.H_ID are primary keys. The corasonding child fields are also indexed.

I use two fields from Races in the Where part; a date field and a char() both are indexed

The where looks like

Where R_Date>'01/01/2001' and AgeRange = '2yo'



Can someone give a simple example for a correctly implemented m-m relationship ?


Can I assume that I am doing something wrong, but that with the right setup mySQL with perform just as well as Access ?

correct structure? yours is correct, even if you have a superfluous surrogate key in the performances table -- take it out, declare the primary key as the composite of the two foreign keys, that will give an index on (r_id,h_id) or (h_id,r_id), whichever order you declare the pk in, then just add another separate index for the second of the two keys

you could also have an index on any fields used in the WHERE, but i see you already did that

Access may have done it by determining only the first group of results and displaying them, while continuing to produce the rest of the results in the background.

To determine how fast Access really is, you must immediately press the "Last Record" button as soon as the results pop up. Since you want to see the last record, Access is obliged to finish the query.

Thanks r937

Your advice improved things dramiticly.

It turned out that I wasn't using the indexs properly at all. The problem query is now 3 or 4 times faster than Access.

sundialcvs, yes that makes sense. thanks

Brian, for the edification of others who'll read this post, can you summarize for us the "before" and "after" picture of exactly what you did?

My basic problem was that I didn't understand how indexes in mySQL work. MS access seems to do it all for you so you become lazy .

When i started i had seperate index for each field I joined on. this gave every poor results.

the explain told me the query was scaning All records.

Then r937 told me to change the indexs with this advice...

even if you have a superfluous surrogate key in the performances table -- take it out, declare the primary key as the composite of the two foreign keys, that will give an index on (r_id,h_id) or (h_id,r_id), whichever order you declare the pk in, then just add another separate index for the second of the two keys.

This helped a lot.

The order of the two indexes was significant,I use table Races in my where part, so its works better with that one first ie(races.R_ID,Horses.H_ID).


then I did a Analyse table on the middle table of my m-m relationship.
This made a real difference again.

I'm sorry I cant show you the explains and all that, I'm in college at the moment and my db is at home.

I wish I could be more precise than that but it's a fuzzy in my own mind at the moment.

The lesson i've learned here is start your sql simplly,one table at a time,one index at a time, and do an EXPLAIN at each increment.

the EXPLAIN feture is really usefull as a learning tool.