MySQL Forums
Forum List  »  PHP

Re: many to many relationships and PHP
Posted by: Scotty Miller
Date: May 07, 2009 12:53PM

One thing I discovered when I was running some benchmark tests for some others to view, to my surprise, if I index both fields in the linking table (yours would be the table with the author_id and book_id), it actually performed slower. Granted it wasn't anything bad because it was still about .05 seconds (calculated it out to 58% percent slower, locally hosted server on the LAN), PLUS I had to combine both fields into one index in order for it to register on the optimizer. One would show as a possible key, but actual key would be blank, unless I combined both into one index. I don't understand that too well, maybe someone else could explain.

The best thing you could do is be adventurous with your queries. I'll frequently take some of my more complicated queries and spend a couple of hours running tests with the MySQL prompt or another GUI tool, running the queries through an extensive quantity, average out the times, then make a single modification at a time, run it through the same quantity, average out the results, so on and so forth until I can't find a better solution for what I am currently achieving. That's the best way to learn use of keys, do the research, become best friends with EXPLAIN, read on how keys work, then apply some, run some queries, change it, run it again, etc...

You're definitely on the right track though. That is the best way to handle multiple relationships between two tables, the use of a linking table.

Options: ReplyQuote


Subject
Written By
Posted
Re: many to many relationships and PHP
May 07, 2009 12:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.