Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: MySQL vs MS Access

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and you have indexes on which fields?

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: MySQL vs MS Access

    Originally posted by brianpower
    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
    Can you post the output of the EXPLAIN as well as a DESCRIBE of the involved tables and a SHOW INDEXES for those tables?

  6. #6
    Join Date
    Jan 2004
    Posts
    8
    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 ?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2003
    Posts
    706

    How the hell did Access do it so fast?
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    Jan 2004
    Posts
    8
    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

  10. #10
    Join Date
    Oct 2003
    Posts
    706
    Originally posted by brianpower
    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.
    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?
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  11. #11
    Join Date
    Jan 2004
    Posts
    8
    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.

Posting Permissions

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