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 quer
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` (`DateOfBirth`)
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 `NameL
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 reason
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 http://dev.mysql.com/doc/re
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 i
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 | possi
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 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, a.Appt
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 ge
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.
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 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
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..
Sebast
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 )
- output of EXPLAIN
sorry for the lack of info.
there are a coupl
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
surely stand a better chance of reply if you provide
information on the above set of questions.
Cheers
Manoj
- Original Message -
From: "Sebastian" <[EMAIL PROTECTED]>
To:
Sent: Thursday, August 11, 2005 3:52 PM
Subject: Re: help with slow query
> no one has any in
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 )
- output of EXPLAIN
This way the list members can make better suggestions.
Regards, Jigal.
-
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, CO
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 (use
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
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 r
22 matches
Mail list logo