, digest
HAVING Sum(Cnt) (Select sum(refcount) from C WHERE tmp.file = C.file and
tmp.digest = C.digest);
--Anupam
--- On Tue, 17/5/11, Aveek Misra ave...@yahoo-inc.com wrote:
From: Aveek Misra ave...@yahoo-inc.com
Subject: Help with a query
To: mysql@lists.mysql.com mysql@lists.mysql.com
Date
and
tmp.digest = C.digest);
--Anupam
--- On Tue, 17/5/11, Aveek Misra
ave...@yahoo-inc.commailto:ave...@yahoo-inc.com wrote:
From: Aveek Misra ave...@yahoo-inc.commailto:ave...@yahoo-inc.com
Subject: Help with a query
To: mysql@lists.mysql.commailto:mysql@lists.mysql.com
mysql
I have a table A and table B of the same type as
CREATE TABLE A (
`file` varbinary(255) not null,
`digest` binary(40) not null
)
CREATE TABLE B (
`file` varbinary(255) not null,
`digest` binary(40) not null
)
I have another table C of the following type
CREATE TABLE C (
?
On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:
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
a POSSIBLE key, right?
On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:
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
` (`NameLast`)
So, the IdPatient is at least a POSSIBLE key, right?
On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote:
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
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
;
- test1 table:
col1v_idh_id
America 1 2
- test2 table:
id name
2 SAM
1 UNCLE
- Original Message -
From: Mark Phillips m...@phillipsmarketing.biz
To: Mysql List mysql@lists.mysql.com
Sent: Monday, July 26, 2010 8:29:00 AM
Subject: Need Help Writing
I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.
Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get
SAM
1 UNCLE
- Original Message -
From: Mark Phillips m...@phillipsmarketing.biz
To: Mysql List mysql@lists.mysql.com
Sent: Monday, July 26, 2010 8:29:00 AM
Subject: Need Help Writing Simple Query
I have been away from sql for awhile, and can't seem to figure out how to
write
On 07/25/2010 09:29 PM, Mark Phillips wrote:
I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.
Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the
Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to
do it. Here is a stripped down version of the result I'm aiming for. I'm pretty
new to queries that act on multiple tables, so apologize if this is a very
stupid question.
I have one table (data) that has two
To: MySql
Subject: Help needed on query on multiple tables
Hi everyone. I'm trying to create a certain MySQL query but I'm not sure
how to do it. Here is a stripped down version of the result I'm aiming
for.
I'm pretty new to queries that act on multiple tables, so apologize
-Original Message-
From: Michael Stroh [mailto:st...@astroh.org]
Sent: June 3, 2010 11:24 AM
To: MySql
Subject: Help needed on query on multiple tables
Hi everyone. I'm trying to create a certain MySQL query but I'm not sure
how to do it. Here is a stripped down version
...@astroh.org]
Sent: June 3, 2010 11:55 AM
To: Steven Staples
Cc: 'MySql'
Subject: Re: Help needed on query on multiple tables
Thanks! That did it perfectly!
Michael
On Jun 3, 2010, at 11:45 AM, Steven Staples wrote:
How about this?
SELECT
`first_table`.`names
What about sub selects. As I see it you only care about the highest
and lowest order of results in each list.
Sorry, in am on a mobile so I can nit make a test case, and this will
be pseudo SQL.
Select * from table where start = (select foo) and ( select foo) ...
Also look at the between
abhishek@gmail.com (Abhishek Pratap) writes:
I am kind of stuck with this query , cant expand my thinking. May this is a
limitation. Here it is
I have a database with many cols two of which are start and end position for
an event.
Now I have a list of event time stamps, I want to
Hi All
I am kind of stuck with this query , cant expand my thinking. May this is a
limitation. Here it is
I have a database with many cols two of which are start and end position for
an event.
Now I have a list of event time stamps, I want to find all the info once the
current event time
aah okie I think I was trying to get too clever. Guess that won't work ...
Thanks,
-Abhi
On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert bboisv...@gmail.comwrote:
You'll have to iterate over your two lists of timestamps and build a
set of ORed conditional pairs:
sql = select ... from ...
why not something like below. Assume you have 3 pairs of start/end
timestamps and you want to find everything within those 3 time periods:
select * from table_name where start = start1 and end = end1
union
select * from table_name where start = start2 and end = end2
union
select * from
Hi Jim
Unfortunately I have thousands of such points. So explicit statement calling
will be very expensive both computationally and in terms of writing..
Thanks,
-Abhi
On Fri, May 8, 2009 at 12:37 PM, Jim Lyons jlyons4...@gmail.com wrote:
why not something like below. Assume you have 3 pairs
Abhi,
I might not be understanding the problem but could you use the max
and min timestamp values and use something like
SELECT * FROM TABLE WHERE start BETWEEN max AND min AND end BETWEEN max AND min
or
SELECT * FROM TABLE WHERE START IN (1,2,3,4,5) AND END IN(1,2,3,4,5)
I might be
then either build the statement by way of a program like a perl script or
select all records with a start time after the min start time of all in your
list and an end time less than the max end time in your list then filter
them further either in a program or a store procedure.
On Fri, May 8,
Hi,
I'm somewhat stumped by how to set up a single query that does the
following. Currently I'm accomplishing this through multiple queries and
some PHP 'glue' logic, but it should be possible in a single query I
think, and it's bugging me that I can't figure it out. If anyone has any
ideas,
Micah,
each item in `a` has a 1 to 1 relationship to `b`,
and each item in `c` has a 1 to 1 relationship with `b`.
Sometimes these correspond, i.e. there's a row in `b`
that relates to both `a` and `c`, but not always.
So in a given b row, the b_id value might match an a.a_id, a c.a_id, or
Peter Brawley wrote:
Micah,
each item in `a` has a 1 to 1 relationship to `b`,
and each item in `c` has a 1 to 1 relationship with `b`.
Sometimes these correspond, i.e. there's a row in `b`
that relates to both `a` and `c`, but not always.
So in a given b row, the b_id value might match an
Hi,
I need some urgent for sql query.. It will be great if someone could help
me..
I have ARTICLE, FAVORITE_ARTICLES, RATING Tables apart from other table
USER, CHANNEL, CATEGORY etc
ARTICLE table stores a user's article, FAVORITE_ARTICLES will store a user's
favorite articles, and rating
Hi:
I have a table that I need help to summarize the data. I need to be able to
create one row of data for custno + prodno + period + weekno combination
summarized by Invtot.
Any help will be greatly appreciated.
Best regards
I am attaching the relevant info below:
Sales
You can just use function sum to get what you want.
On Jan 16, 2008 6:23 AM, Imran [EMAIL PROTECTED] wrote:
Hi:
I have a table that I need help to summarize the data. I need to be able
to
create one row of data for custno + prodno + period + weekno combination
summarized by Invtot.
Hello, I'm trying to get what is for me quite a complicated query to
work, if it's possible to do so anyway ...
Here is my old query :
SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN
info b ON a.from=b.code WHERE a.id='28'
It worked fine untill I needed to have more than
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the
-Original Message-
From: Marcus Claesson [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 7:49 AM
To: mysql@lists.mysql.com
Subject: Help with SQL query construction
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing
Marcus,
I've managed to do this with a Perl-DBI script, but
would much prefer to do it completely with MySQL instead.
You could port it to a recursive stored procedure. It would probably be
slower, and what would you have gained?
PB
Marcus Claesson wrote:
Hi!
I have a SQL query
Hi every:
I have this table:
smp_evaluacion
---
id_evaluacion
eval1
eval2
eval3
eval4
eval5
This is for a rating system. I need to build a query for retrieve the best
rating files. Normally with PHP I add every field, I mean (eval1, eval2, eval3,
eval4, eval5) and
Reynier,
Normally with PHP I add every field, I mean (eval1, eval2,
eval3, eval4, eval5) and then divide this result by 5, like
a average. I know that SQL can do this directly using AVG
function but I don't know how. Can any help me?
AVG computes an average across rows. If I understand you
I'm trying to delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results and then run a series
of delete queries. I believe this is a rather basic issue that has a
well-accepted, simple solution which I am ignorant of. There are two
relevant
Thanks Brent, good tip. Works like a charm.
On Jun 14, 2007, at 7:42 PM, Brent Baisley wrote:
Here's a little trick. Get your DELETE query working as a SELECT.
Then replace everything before FROM with DELETE tablename.
SELECT order_items.ord_id FROM order_items LEFT JOIN orders ON
- Original Message -
From: Ben Liu [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, June 14, 2007 3:11 PM
Subject: need help with delete query
I'm trying to delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results
rows and is 75KB big. It runs
extremely slow. I tried to create an index for it but it failed to increase
performance.
Any help is appreciated.
--
View this message in context:
http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554
Sent from the MySQL - General mailing list
performance.
Any help is appreciated.
--
View this message in context:
http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
.
This `ptsSignups` table contains 82752 rows and is 75KB big. It runs
extremely slow. I tried to create an index for it but it failed to
increase
performance.
Any help is appreciated.
--
View this message in context:
http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554
Sent
and is 75KB big. It runs
extremely slow. I tried to create an index for it but it failed to
increase
performance.
Any help is appreciated.
--
View this message in context:
http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554
Sent from the MySQL - General mailing list archive
is appreciated.
--
View this message in context:
http://www.nabble.com/Help-indexing-this-query.-tf3059045.html#a8505554
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http
-Original Message-
From: Brian Dunning [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 07, 2007 1:12 PM
To: mysql
Subject: Help optimizing this query?
This is the query that's killing me in the slow query log, usually
taking around 20 seconds:
select count(ip) as counted,stamp from
Message-
From: Brian Dunning [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 07, 2007 1:12 PM
To: mysql
Subject: Help optimizing this query?
This is the query that's killing me in the slow query log, usually
taking around 20 seconds:
select count(ip) as counted,stamp from ip_addr where stamp=NOW
with it). However, more indices = more overhead,
which is sometimes a problem, sometimes not.
Dan
On 1/8/07, Michael Gargiullo [EMAIL PROTECTED] wrote:
-Original Message-
From: Brian Dunning [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 07, 2007 1:12 PM
To: mysql
Subject: Help
This is the query that's killing me in the slow query log, usually
taking around 20 seconds:
select count(ip) as counted,stamp from ip_addr where stamp=NOW()-
interval 14 day and source='sometext' group by stamp order by stamp
desc;
Here is the table:
CREATE TABLE `ip_addr` (
`ip`
Brian Dunning wrote:
This is the query that's killing me in the slow query log, usually
taking around 20 seconds:
select count(ip) as counted,stamp from ip_addr where
stamp=NOW()-interval 14 day and source='sometext' group by stamp
order by stamp desc;
Here is the table:
CREATE TABLE
Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Ferindo Middleton [mailto:[EMAIL PROTECTED]
Sent: Saturday, October 14, 2006 9:16 PM
To: Dan Buettner
Cc: mysql
Subject: Re: help with update query
Thanks Dan
Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Ferindo Middleton [mailto:[EMAIL PROTECTED]
Sent: Saturday, October 14, 2006 9:16 PM
To: Dan Buettner
Cc: mysql
Subject: Re: help with update query
Thanks Dan. This does
Ferindo, I had a similar task recently, and the problem you'll run
into is that you can't select from and update the same table at once.
What I ended up doing was doing a SELECT to build the update queries
for me.
Something like this:
SELECT CONCAT(
UPDATE bowler_score SET email_address = ',
Thanks Dan. This does help. This a pretty straight-forward idea. I could
even save the results of this query to a text file and possibly review it a
little before running it so I don't acidentally do anything funky and I
could see the impact this would have on the data before applying it. I
Good call on the WHERE email_address IS NULL thing. Also occurs to me
you could do a SELECT DISTINCT instead of just a SELECT to eliminate
duplicate update commands.
Glad this was useful.
Dan
On 10/14/06, Ferindo Middleton [EMAIL PROTECTED] wrote:
Thanks Dan. This does help. This a pretty
I have a table, bowler_score_records, with the following columns: id,
firstname, middlename, lastname, race, religion, email_address,
bowling_score, gamedate
As records get entered to this table, sometimes the users forget to input
the email_address but the users always capture the full name,
Hi All,
Given this table:
+-+-+++-
|sip_status | sip_method | sip_callid | username |
fromtag| totag
, 2006 12:25:50 PM CDT
To: Joseph Alotta [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: please help optimize this query
I'd start by looking at your schema.. Quite a lot of use of
varchars... I'd suggest using chars (takes more space but processes
faster).. Or even better, normalize
Greetings,
I generate this report of all the holdings by symbol, summing up over
accounts. It is taking much too long. I was wondering if I can add
an index on something to make it more efficient. The first query
gets the most recent date, the next query sums it up by symbol.
Thank
I'd start by looking at your schema.. Quite a lot of use of
varchars... I'd suggest using chars (takes more space but processes
faster).. Or even better, normalize the data so that you have a table
for symbols that is linked to this table via an integer based foreign
key.
Also it seems wierd
Hello,
Not sure if i can do this.
I have a table with a datetime column
I would like to do group by a day of the month.
if i do something like
select count(*) from MTracking where mallarea=1001 group by timeofclick
every one is listed because time.
So is this possible?
Thanks
Randy
--
try:
group by substring(timeofclick,1,10)
-Original Message-
From: Randy Paries [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 03, 2006 11:25 AM
To: mysql@lists.mysql.com
Subject: Help with this query. How to do a group by on a datetime just
the month/day/year
Hello,
Not sure if i
select count(*), substring(timeofclick,1,7) from MTracking where
mallarea=1001 group by 2;
On 5/3/06, Randy Paries [EMAIL PROTECTED] wrote:
Hello,
Not sure if i can do this.
I have a table with a datetime column
I would like to do group by a day of the month.
if i do something like
I am running mysql 4.0.24 on Debian sarge.
I have a table with two columns, team and division, both varchar(255).
There are some errors in the table where division has a value but team is
blank. Given that I am getting new data, and the data entry folks may create
a record with a blank
Mark Phillips wrote:
I am running mysql 4.0.24 on Debian sarge.
I have a table with two columns, team and division, both varchar(255).
There are some errors in the table where division has a value but team is
blank. Given that I am getting new data, and the data entry folks may create
a
On Monday 23 January 2006 03:33 pm, Michael Stassen wrote:
Mark Phillips wrote:
I am running mysql 4.0.24 on Debian sarge.
I have a table with two columns, team and division, both varchar(255).
There are some errors in the table where division has a value but team is
blank. Given
Hello.
You may use these queries:
select flight_id
,baseline*tan(radians(angle)) as attitude
from flights
where (baseline*tan(radians(angle))) =
( select max(baseline*tan(radians(angle)))
from flights f2);
I have a table with several columns. The ones of interest are flight_id,
angle, and baseline. I want to find the flight_ids for the flights with the
maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)).
For example,
Flights
+++---+
Oops, I meant to copy the list on this reply so that others could
benefit
Rhino
- Original Message -
From: Rhino [EMAIL PROTECTED]
To: Mark Phillips [EMAIL PROTECTED]
Sent: Sunday, December 11, 2005 6:19 PM
Subject: Re: Need Help with a query
- Original Message
I forgot to copy the list as well
Mark
-- Forwarded Message --
Subject: Re: Need Help with a query
Date: Sunday 11 December 2005 06:47 pm
From: Mark Phillips [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Rhino,
My apologies for leaving out the version of mysql. I agree
Hello,
I am trying to put together a single query from across 4 tables and not having a
problem getting the results needed. table structure:
##
CREATE TABLE IF NOT EXISTS job_posts (
jobid int(6) unsigned NOT NULL auto_increment,
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 08:34:56 AM:
Hello,
I am trying to put together a single query from across 4 tables and
not having a
problem getting the results needed. table structure:
##
CREATE TABLE IF NOT EXISTS
Hello,
[EMAIL PROTECTED] wrote:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 08:34:56 AM:
Hello,
I am trying to put together a single query from across 4 tables and
not having a
problem getting the results needed. table structure:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 10:08:24 AM:
Hello,
[EMAIL PROTECTED] wrote:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 08:34:56
AM:
Hello,
I am trying to put together a single query from across 4 tables and
not having a
problem getting the
[EMAIL PROTECTED] wrote:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 10:08:24 AM:
Hello,
[EMAIL PROTECTED] wrote:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 08:34:56
AM:
Hello,
I am trying to put together a single query from across 4 tables and
not having a
Hello,
I need some help optimizing a query. The current query is as follows:
SELECT *,
MATCH(title) AGAINST ( 'S' IN BOOLEAN MODE ) AS score
FROM articles
WHERE MATCH(title) AGAINST ( 'S' IN BOOLEAN MODE );
'title' is a FULLTEXT index.
'S' is a query string that may have 100 search
Brosnan wrote:
Hello,
I need some help optimizing a query. The current query is as follows:
SELECT *,
MATCH(title) AGAINST ( 'S' IN BOOLEAN MODE ) AS score
FROM articles
WHERE MATCH(title) AGAINST ( 'S' IN BOOLEAN MODE );
'title' is a FULLTEXT index.
'S' is a query string that may have
Mysql's query cache has two option's [query_cache_limit] and [query_cache_size].
The documentation is not clear (for me) on their purpose and correct
usage tactics.
What does the [query_cache_size] sets, the maximum memory space a
single query can utilize ? Is it wise to set it to the same size
query_cache_size - a total size of memory that server utilizes for
query caching.
query_cache_limit - maximum size of result set that can be cached.
I'd suggest leaving query_cache_limit at 1 mb (the default value), and
tune query_cache_size until you have reasonably low
qcache_lowmem_prunes
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..
might help. Also making the query ALTER TABLE maps ORDER BY
maps.dateline DESC once a day or more, would help the ordering.
I may be saying too include much indexes (probably the first), but it
may not make bad at all (probably updates/inserts would be slower).
Making some tests might help to see
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
I need to speed up a search, big time.
I have an application that searches for records on a date field. If it
doesn't find an exact date match, it keeps searching adjacent days until it
finds a certain amount of records.
The problem now is, I'm using my application to loop through and run
Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22:
I need to speed up a search, big time.
I have an application that searches for records on a date field. If it
doesn't find an exact date match, it keeps searching adjacent days until
it
finds a certain amount of records.
The
[EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM:
Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22:
I need to speed up a search, big time.
I have an application that searches for records on a date field. If
it
doesn't find an exact date match, it keeps searching adjacent
[EMAIL PROTECTED] wrote on 16/06/2005 16:29:46:
[EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM:
Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22:
I need to speed up a search, big time.
I have an application that searches for records on a date field. If
it
(forwarded to the list - I am not the OP)
- Forwarded by Shawn Green/Unimin on 06/16/2005 11:45 AM -
James Black [EMAIL PROTECTED] wrote on 06/16/2005 11:44:36 AM:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
[EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] wrote on 06/16/2005 11:30:10
At 03:09 AM 6/16/05, Cory Robin wrote:
I need to speed up a search, big time.
I have an application that searches for records on a date field. If it
doesn't find an exact date match, it keeps searching adjacent days until it
finds a certain amount of records.
The problem now is, I'm using my
hi,
that's the same. If you use between, mysql do the rest for you :
mysql explain SELECT * FROM passengers WHERE
- reservation_date_time = '2005-01-01 12:10:00'
- AND reservation_date_time = '2005-05-01 12:10:00';
1 - 100 of 235 matches
Mail list logo