Re: Optimize Question
I'm running 3.23.52 locally, but I just noticed my production server is running 4.0.12. Given the complexity of this query, should I use subqueries instead of joins? Would that make any difference? Best, Patrick > What version of mysql is this? If you're running 3.23, does 4.0.14 do the > same thing? And if you can try it, does 4.1.0? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize Question
I just tried that, but with mixed results. Like before, when I added an index on publish, I get a 'range' join, but it's still doing filesort. > | theater | range | status_publish | 1 | NULL | 4279 | where used; Using filesort Any way around that? Best, Patrick > An index on t (publish,photo,name_present,location_city) would be the > most effiecient, I think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize Question
Hi all!
I'm trying to optimize the statement below, but I can't figure out why this
query is still doing an ALL join with a filesort.
There are several indexes on both my main table ('t'), and all the tables
I'm trying to join. (I also tried adding an index for t.publish, which
bumped my join type to 'range' but still gave me a 'filesort' and seemed to
produce slower query results.)
What can I do to further optimize?
Thanks,
Patrick
...
**SELECT STATEMENT**
SELECT
t.id,
t.name_present,
t.location_city,
t.photo,
a.name_short,
a.name_long,
b.name as b_name,
c.name as c_name,
d.id as d_id,
d.name as d_name
FROM t
LEFT JOIN a
ON t.a_id = a.id
LEFT JOIN b
ON t.b_id = b.id
LEFT JOIN c
ON t.c_id = c.id
LEFT JOIN d
ON t.d_id = d.id
LEFT JOIN e_t
ON e_t.t_id = t.id
LEFT JOIN e
ON e_t.e_id = e.id
WHERE t.publish = 'Yes' AND e.id = '2'
ORDER BY t.photo, t.name_present, t.location_city
LIMIT 0,25
**EXPLAIN RESULTS FOR THIS QUERY**
+-++-+-+--+--+-+
| tbl | type | key | key_len | ref | rows | Extra |
+-++-+-+--+--+-+
| t | ALL| NULL|NULL | NULL | 4279 | where used; filesort|
| a | eq_ref | PRIMARY | 2 | t.a_id |1 | |
| b | eq_ref | PRIMARY | 2 | t.b_id |1 | |
| c | eq_ref | PRIMARY | 1 | t.c_id |1 | |
| d | eq_ref | PRIMARY | 1 | t.d_id |1 | |
| e | eq_ref | PRIMARY | 1 | e_t.e_id |1 | where used; index |
| e_t | ref| t_id| 2 | t.id |1 | |
+-++-+-+--+--+-+
('possible_keys' was identical to 'key', but i removed it due to space
issues)
**INDEXES ON 't'**
+---+---+
| Key_name | Column_name |
+---+---+
| PRIMARY | id|
| location_city | location_city |
| name_present | name_present |
| a_id | a_id |
| b_id | b_id |
| c_id | c_id |
| d_id | d_id |
+---+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
> Sounds as if your table is not normalized for that kind of query. If > there are multiple directors id'd by multiple movies you could left > outer join the movie.id with the director.id > > Make sense? Actually, it's normalized beyond that. Sometimes, movies have more than one director, so the association between movies and directors needs to be in its own table, instead of the movies table. (Otherwise, you're limited to some fixed number of directors per film.) Directors may not be the best example. Think about producers, cast, etc. Let's say I need to pull all producers for a movie. For a movie that has three producers (aka three matches with a LEFT OUTER JOIN), I'll get three rows -- all with the same movie, but different producers. Jurassic Park | Kathleen Kennedy | 35 comments Jurassic Park | Gerald R, Molen | 35 comments Jurassic Park | Lata Ryan| 35 comments How can I list all of these producers within a single row? Jurassic Park | Kathleen Kennedy, Gerald R, Molen, Lata Ryan | 35 comments Best, Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are my tables: movies directors comments movies_directors movies_comments etc... (primary key is movies.id) But here's where I'm stuck: for each film, I need to pull the movie info in 'movies', plus any related data from other tables, like this: The Lord of the Rings | Peter Jackson | 3 comments Episode II| George Lucas | 0 comments Indiana Jones | Steven Spielberg | 15 comments I seem to run into problems when I try to join info from all of these related tables. If there's a match, great. But, if not (like a movie with no comments), the movie is excluded from the result set. I've tried all sorts of SQL queries to make this work, but nothing seems to do the trick. SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the subquery lookups? Thanks for your help! Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
Thanks, Jay and Andy! OUTER JOINS work great for part of my query. But how would you modify this query to support multiple directors? (That's why I've got a movie_director table.) Movie Name | Director A, Director B, etc. | 35 comments Best, Patrick > SELECT foo > FROM movie LEFT OUTER JOIN director > ON movie.director = director.name > LEFT OUTER JOIN > ON movie.id = comments.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
The comma after Gerlad R was a typo.
As for the 500 actors issue, I'm merely using the movie db as an example.
People always post the strangest db's to this list ("I have one table for
monkeys and one for fishes who speak Urdu, and I need..."), so I thought I'd
spare everyone the particulars of mine.
I'm quite eager to use some of the 4.0/4.1 features, but for now, I have to
play in the 3.23 sandbox.
Ultimately, I'd like single rows here, but will use PHP to compact the rows
for now.
Thanks for your help everyone!
Patrick
> I can't say I like the idea of concatenating all those items into one.
> Look at the parsing difficulty you've gotten yourself into. Is "Molen"
> a director? Or is it "Gerald R"?
> Most databases don't have any functions for this grouping-concatenation,
> since it leaves you with a result set that has a field of arbitrary
> length. What about a movie with 500 actors?
> So, really, I prefer to order by the movie, then the director. While
> processing the result set, just be aware you might have duplicate
> consecutive movies. Then you can roll in the directors as you go.
>
> So much for that lecture. If you're willing to use the ALPHA mySQL 4.1,
> there is a method that promises to do what you want:
> http://www.mysql.com/doc/en/GROUP-BY-Functions.html, look at
> GROUP_CONCAT. I think that fits the bill, with the caveat that it might
> well not be ready for prime-time yet. Oh, and it won't work on (any)
> other databases.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spam
I have to say I'm shocked that the list is completely open. That's such a stupid newbie thing to do. Maybe we should initiate a spam strike until the list mom/dad listens. Just subscribe the list to *another* list, and watch the fun begin... best, patrick crowley mokolabs>> making sure the future doesn't suck... w > http://mokolabs.com e > [EMAIL PROTECTED] p > 646.591.5477 > Why *isn't* posting to the MySQL list restricted to subscribers? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
