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
