Graham Anderson <[EMAIL PROTECTED]> wrote on 03/23/2005 08:09:48 PM: > I have 3 different tables I need data from.... > And, the tables have the potential to get fairly large > I am using mysql 4.1.3 > > This working query below pulls up all media requests for 'Yolanda > Perez' in Los Angeles > Is there a appreciably speedier way to say the below ? > I have begun dipping my toe into more complicated queries > > Select artist.name,media.name,userLog.city,userLog.DateTime > From userLog,media,artist > Where city = 'Los Angeles' And > userLog.media_id = media.id And > media.artist_id=artist.id And > artist.name = 'Yolanda Perez' > > > > > many thanks > g >
You can get MySQL to describe for you if it will be using any indexes in the execution of that query or not by using the EXPLAIN command. If your EXPLAIN does not show any indexes are to be used, that is probably the reason your query does not perform well. http://dev.mysql.com/doc/mysql/en/explain.html EXPLAIN Select artist.name,media.name,userLog.city,userLog.DateTime From userLog,media,artist Where city = 'Los Angeles' And userLog.media_id = media.id And media.artist_id=artist.id And artist.name = 'Yolanda Perez'; One thing I suggest (my opinion only) that you stop using right now is the comma-list form of creating INNER JOINs. In your query you implicitly INNER JOIN 3 tables: userLog, media, and artist. To re-write that query using explicit inner joins you would say: SELECT artist.name,media.name,userLog.city,userLog.DateTime FROM userLog INNER JOIN media ON userLog.media_id = media.id INNER JOIN artist ON media.artist_id=artist.id WHERE city = 'Los Angeles' AND artist.name = 'Yolanda Perez' http://dev.mysql.com/doc/mysql/en/join.html If you ever want to use one of the "outer" JOINs (a LEFT JOIN or a RIGHT JOIN), you will have to use the explicit form anyway as the implicit form does not support outer JOINs. I prefer the explicit form because it makes it much harder for me to accidentally leave out a JOINing condition because they all reside within their own ON clauses. In the implicit form, they are comingled with the other WHERE clauses and can be more easily overlooked. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
