Hi,
I'd try to `optimize' the tables in the first place.
Have you tried to select and group by PR1.pollResponsesID instead of
PR2.pollResponsesID?
As they are the equal, the result should be the same, but the query might get
optimized better.
Hope this helps,
Jan
"Oson, Chris M." wrote:
>
> Hello,
>
> On a related matter, do the various JOIN's (LEFT, INNER, and OUTER) use a
> temp file
> when processing?
>
> Table pollResponses:
> +-----------------+---------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------------+---------------+------+-----+---------+----------------+
> | pollResponsesID | int(11) | | PRI | NULL | auto_increment |
> INDEXED FIELD
> | pollID | int(11) | | MUL | 0 | |
> INDEXED FIELD
> | storyID | int(11) | YES | MUL | NULL | |
> | pollResponse | varchar(255) | | | | |
> | dateEntered | timestamp(14) | YES | | NULL | |
> +-----------------+---------------+------+-----+---------+----------------+
>
> Table pollResults:
> +-----------------+---------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------------+---------------+------+-----+---------+----------------+
> | pollResultsID | int(11) | | PRI | NULL | auto_increment |
> | pollID | int(11) | | | 0 | |
> | pollResponsesID | int(11) | | MUL | 0 | |
> INDEXED FIELD
> | ipAddress | char(16) | | MUL | None | |
> INDEXED FIELD
> | dateEntered | timestamp(14) | YES | | NULL | |
> +-----------------+---------------+------+-----+---------+----------------+
>
> Table A has 1400 rows whereas Table B has 190,000 rows. It's a site that
> keeps track of web polls
> covering certain issues regarding daily events.
>
> Here's my query....
>
> SELECT PR2.pollResponsesID, PR1.pollResponse, COUNT(PR2.pollResponsesID) AS
> votes
> FROM pollResults PR2
> INNER JOIN pollResponses PR1 ON PR1.pollResponsesID =
> PR2.pollResponsesID
> WHERE PR1.pollID = $pollID <<< This is a value passed by a query
> string....
> GROUP BY PR1.pollResponse, PR2.pollResponsesID
> ORDER BY PR2.pollResponsesID
>
> The site I work on gets alot of traffic and sometimes the traffic surges big
> time. Unfortunately, the
> server has both the database server and the web server on the same machine
> until it's moved over to a
> different setup with the database/web server on different machines.
>
> There's another guy who says that this query kills the database server
> because it's a join and uses a temp file. (Although
> in the documentation, Using temporary uses a temp table)
>
> Is mySQL really the best db to handle these types of queries? I really like
> using it and would rather
> not have to switch.
>
> There are indexes to speed up this query.
>
> mysql> explain
> -> SELECT PR2.pollResponsesID, PR1.pollResponse,
> COUNT(PR2.pollResponsesID) AS votes
> -> FROM pollResults PR2
> -> INNER JOIN pollResponses PR1 ON PR1.pollResponsesID =
> PR2.pollResponsesID
> -> WHERE PR1.pollID = 211
> -> GROUP BY PR1.pollResponse, PR2.pollResponsesID
> -> ORDER BY PR2.pollResponsesID;
> +-------+------+------------------------------+----------------------+
> | table | type | possible_keys | key |
> +-------+------+------------------------------+----------------------+
> | PR1 | ref | PRIMARY,pollResponses_pollID | pollResponses_pollID |
> | PR2 | ref | pRID | pRID |
> +-------+------+------------------------------+----------------------+
> +---------+---------------------+------+------------------------------------
> ---------+
> | key_len | ref | rows | Extra
> |
> +---------+---------------------+------+------------------------------------
> ---------+
> | 4 | const | 1 | where used; Using temporary; Using
> filesort |
> | 4 | PR1.pollResponsesID | 540 | Using index
> |
> +---------+---------------------+------+------------------------------------
> ---------+
>
> The home page has 7 queries on small tables also. Is that too many queries
> to be doing on one home page?
>
> Any input would be appreciated...
>
> Christopher Oson
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
