Shawn,
Thanks for the great help! It still is not working. I did an EXPLAIN on this
query with your amended split out join statements and got this:
++-+---+---+---++-+--++-+
| id | select_type | table | type |
Rhino,
Thanks for the help and time! Actually, I thought the same thing, but what's
weird is that is the only thing that doesn't slow it down. If I take out all of
the join clauses EXCEPT that one the query runs virtually instantaneously. for
some reason it will use the index in that case and
If the optimizer chooses the wrong index, you can tell it what index to use.
SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI
from Appt_ a force index(id_patient)
LEFT JOIN patient_ p
ON a.IdPatient = p.IdPatient
WHERE a.ApptDate = '2009-03-01';
See
On 3/10/2011 12:32, Jim McNeely wrote:
Rhino,
Thanks for the help and time! Actually, I thought the same thing, but what's
weird is that is the only thing that doesn't slow it down. If I take out all of
the join clauses EXCEPT that one the query runs virtually instantaneously. for
some
Shawn,
This is the first thing that I though as well, but here is a portion from the
show create table for patient_:
PRIMARY KEY (`zzk`),
KEY `IdPatient` (`IdPatient`),
KEY `SSN` (`SSN`),
KEY `IdLastword` (`IdLastword`),
KEY `DOB` (`DateOfBirth`),
KEY `NameFirst` (`NameFirst`),
KEY
On 3/10/2011 13:12, Jim McNeely wrote:
Shawn,
This is the first thing that I though as well, but here is a portion from the
show create table for patient_:
PRIMARY KEY (`zzk`),
KEY `IdPatient` (`IdPatient`),
KEY `SSN` (`SSN`),
KEY `IdLastword` (`IdLastword`),
KEY `DOB`
On 3/10/11 10:46 AM, Shawn Green (MySQL) wrote:
On 3/10/2011 12:32, Jim McNeely wrote:
Rhino,
Thanks for the help and time! Actually, I thought the same thing, but what's
weird is that is the only thing that doesn't slow it down.
If I take out all of the join clauses EXCEPT that one the
I am trying to set up an export query which is executing very slowly, and I was
hoping I could get some help. Here is the query:
SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI,
a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, , a.ApptTimeOut) AS CHAR)
ApptDateTime,
Hi Jim,
On 3/9/2011 17:57, Jim McNeely wrote:
I am trying to set up an export query which is executing very slowly, and I was
hoping I could get some help. Here is the query:
SELECT a.IdAppt, a.IdPatient,
p.NameLast, p.NameFirst, p.NameMI,
a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, ,
I know it's bad form to reply to yourself but I just found a major mental
mistake in my response. See embedded:
[EMAIL PROTECTED] wrote on 08/12/2005 12:18:21 AM:
Sebastian [EMAIL PROTECTED] wrote on 08/11/2005 01:19:30
PM:
well i managed to solve the problem myself, and im no sql
no one has any info to help me out?
all i need to know if there is a way to speed up the query or will i
have to live with it.
this query runs slow because AVG and COUNT on maps_rating table i think.
can anything be done to improve?
query:
SELECT
maps.*, AVG(maps_rating.rating) AS rating,
Sebastian wrote:
this query runs slow because AVG and COUNT on maps_rating table i think.
can anything be done to improve?
You may want to include:
- table definitions (output of SHOW CREATE TABLE table)
- output of EXPLAIN query
This way the list members can make better suggestions.
a better chance of reply if you provide
information on the above set of questions.
Cheers
Manoj
- Original Message -
From: Sebastian [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, August 11, 2005 3:52 PM
Subject: Re: help with slow query
no one has any info to help me out
Hello.
I've created tables similar to your and the query runs fast enough
on my test data (maybe I have good indexes). Please, provide the
EXPLAIN output for your query and exact definitions of your tables
(use SHOW CREATE TABLE).
Sebastian [EMAIL PROTECTED] wrote:
this query runs
Jigal van Hemert wrote:
Sebastian wrote:
this query runs slow because AVG and COUNT on maps_rating table i think.
can anything be done to improve?
You may want to include:
- table definitions (output of SHOW CREATE TABLE table)
- output of EXPLAIN query
sorry for the lack of info.
there
well i managed to solve the problem myself, and im no sql genius...
i thought i had an index on maps_rating.map which i didn't.. adding an
index on it improved the query.
i think that is about all the improvement i can get.. but if there is
still room for more speed i'd like to know..
Sebastian wrote:
well i managed to solve the problem myself, and im no sql genius...
i thought i had an index on maps_rating.map which i didn't.. adding an
index on it improved the query.
i think that is about all the improvement i can get.. but if there is
still room for more speed i'd like
Hello.
i thought i had an index on maps_rating.map which i didn't.. adding an
index on it improved the query.
Have a look here:
http://dev.mysql.com/doc/mysql/en/order-by-optimization.html
http://dev.mysql.com/doc/mysql/en/group-by-optimization.html
Sebastian [EMAIL
Sebastian [EMAIL PROTECTED] wrote on 08/11/2005 01:19:30 PM:
well i managed to solve the problem myself, and im no sql genius...
i thought i had an index on maps_rating.map which i didn't.. adding an
index on it improved the query.
i think that is about all the improvement i can get.. but
this query runs slow because AVG and COUNT on maps_rating table i think.
can anything be done to improve?
query:
SELECT
maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS
votes, user.username
FROM maps
LEFT JOIN maps_rating ON (maps.id = maps_rating.map)
LEFT JOIN user ON
Help: (and apologies if this is posted to the wrong list..)(pls let me know
where to post if so.. Thx ;-)
I have *inherited* an App that uses PHP / MySQL. THe internal search function
within the application
that I am supporting uses the following DB Table structure and runs
the Query below to
You are always getting filesort, where and temporary because the
optimizer cannot use an index. Both your WHERE clause and your ORDER BY
clause use computed values. None of those values exist in an index because
you calculate them for every query.
What I did below is not a refactoring, just a
22 matches
Mail list logo
