Another question is that if I run such CREATE TEMPORARY statements in my
query, is MySQL really can do it fast?
Cause this query may be run periodically !
On 2/2/06, سيد هادی راستگوی حقی [EMAIL PROTECTED] wrote:
Thanks for your suggestion,
I forget to tell that each message in traffic_log may
In addition to the id_Site, you also need to grab the MAX(Time) so you have
something to sort by. This requires a little trick known as a groupwise
maximum. See
http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html for
an explanation and some examples.
Greg Fortune
On
Ian Barnes wrote:
Hi,
This is my current query which works in mysql 4, but not in 5. Its from
mambo, but im trying to modify it because they don't officially support
mysql5 yet.
The original query:
SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title
, cc.title, c.ordering LIMIT 0,10;
Thanks,
Ian
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: 24 January 2006 09:50 PM
To: Ian Barnes
Cc: mysql@lists.mysql.com
Subject: Re: Query Help
Ian Barnes wrote:
Hi,
This is my current query which works in mysql 4
PM
To: Ian Barnes
Cc: mysql@lists.mysql.com
Subject: Re: Query Help
Ian Barnes wrote:
Hi,
This is my current query which works in mysql 4, but not in 5. Its from
mambo, but im trying to modify it because they don't officially support
mysql5 yet.
The original query:
SELECT c
Subject: Re: Query Help
Ian Barnes wrote:
Hi,
This is my current query which works in mysql 4, but not in 5. Its from
mambo, but im trying to modify it because they don't officially support
mysql5 yet.
The original query:
SELECT c.*, g.name AS groupname, cc.name, u.name AS editor
Dan,
I'm trying to generate a list of sites that HAD a support incident within a
known date range, and order them so that the site that has the OLDEST
support call is FIRST in the list.
It's the (oft-asked-for) groupwise-max query. Here's one way, assuming
you have MySQL 4.1 or later ...
Peter Brawley [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Dan,
I'm trying to generate a list of sites that HAD a support incident within
a known date range, and order them so that the site that has the OLDEST
support call is FIRST in the list.
It's the (oft-asked-for)
Douglas S. Davis wrote:
Hi,
If the following isn't appropriate, please feel free to ignore. The
program I'm referring to is written in Perl and uses a MySQL database,
so I thought perhaps it would be appropriate for this list.
I have a webpage that displays a user's profile by selecting
Hi Tripp, all!
Tripp Bishop wrote:
[[...]]
Why would adding extra fields to the query slow the
query down so much? In either case it looks like it's
using the same index.
If a query is to return only fields contained in the index, it can be
executed as an index-only query, and the true
It might be a good idea if you could post the results of an EXPLAIN SELECT ...
for a fast query and a slow query along with their actual SQL statements.
James Harvard
At 11:37 am -0800 12/1/06, Tripp Bishop wrote:
I've got a question regarding optimizing a query.
--
MySQL General Mailing List
Jim, here's an example query:
SELECT COUNT(listing.listing_id) AS listing_count FROM
listing_city_xref INNER JOIN listing
ON listing.listing_id = listing_city_xref.listing_id
AND listing.group_id = listing_city_xref.group_id
WHERE listing_city_xref.group_id =2
AND listing_city_xref.city_id IN
You should be able to join the tables like this:
select p1.id /* and other cols */
from people1 p1
inner join people2 p2 on p1.Lname = p2.Lname and left(p1.Fname, 3) =
left(p2.Fname, 3)
group by p1.id;
HTH, James Harvard
I have two independently built tables of people. I am trying to match
nm ... I found the problem ... I need to use BINARY(LPAD ...)
-Original Message-
From: Rob Brooks [mailto:[EMAIL PROTECTED]
Sent: Monday, December 19, 2005 10:04 AM
To: mysql@lists.mysql.com
Subject: Query Error Help
Hello, I have this query:
SELECT FL3_PatientControlNumber,
Thanks to all those who provided feed back.
As a result I found two ways of solving my problem, one is going along the LEFT
JOIN track, and the other one is to run a script before doing the query itself
whioch is doable in my environment without modifying the core software and
which can then do
Jacques Brignon [EMAIL PROTECTED] wrote on 11/09/2005 09:58:07 AM:
I have a query which scans a subscription databse to locte the most
recent
expiration date of the subscription to a given periodical or serviceto
compute
the start date of a renewal.
It works fine when for a given person
Thanks, that makes a lot of sense.
My only problem is that I am using here a standard piece of code on the
application side and I would hate to modify it, the thing I have all liberty to
change is the query! Reason for trying to ask the query itself to tell me if
there is nothing in the DB!
--
In article [EMAIL PROTECTED],
Jacques Brignon [EMAIL PROTECTED] writes:
Thanks, that makes a lot of sense.
My only problem is that I am using here a standard piece of code on the
application side and I would hate to modify it, the thing I have all liberty
to
change is the query! Reason for
: mysql@lists.mysql.com
Subject: Re: Query producing default values
Thanks, that makes a lot of sense.
My only problem is that I am using here a standard piece of code on the
application side and I would hate to modify it, the thing I have all
liberty to
change is the query! Reason for trying to ask
[EMAIL PROTECTED] wrote:
Jacques Brignon [EMAIL PROTECTED] wrote on 11/09/2005 09:58:07 AM:
I have a query which scans a subscription database to locate the most recent
expiration date of the subscription to a given periodical or service to compute
the start date of a renewal.
It works fine
[EMAIL PROTECTED] wrote on 10/18/2005 06:28:03 AM:
Hello,
CREATE TABLE t (
dt datetime
)
Please assume this is the table structure and contains thousands of
records.
And I want to list them only last 12 months (that mean last 1 year)
records
exactly.
For that I tried using the
R,
I tried using the following query, but it list sometimes 13 months
when the current date is in the middle of the month.
SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW();
Try...
DATE_ADD( dt, INTERVAL
IF(YEAR(NOW())%40 AND YEAR(NOW())%100=0,366,365)
DAY ) = NOW()
grKumaran wrote:
Hello,
CREATE TABLE t (
dt datetime
)
Please assume this is the table structure and contains thousands of
records. And I want to list them only last 12 months (that mean last 1
year) records exactly.
For that I tried using the following query, but it list sometimes 13
Gleb Paharenko wrote:
Hello.
I get 142k rows back in just over 4 seconds.
It means that on the server side query works, so the problem
probably is in PHP.
Good call. I did some testing, and sure enough, the mysql_query() is
successful. The failure comes in trying to retrieve the rows
Hello.
I get 142k rows back in just over 4 seconds.
It means that on the server side query works, so the problem
probably is in PHP.
However, when I run this query using PHP through the browser, I get
nothing at all. Occasionally, I get get an error about not being able
to store
[EMAIL PROTECTED] wrote:
lets say i have two tables:
module
-
VARCHAR module_name
INTEGER module_id
module_config
INTEGER module_id
VARCHAR config_name
VARCHAR config_value
config item names and values are rather arbitrary and depend on the module.
each module can
[EMAIL PROTECTED] wrote on 10/11/2005 01:27:12 PM:
lets say i have two tables:
module
-
VARCHAR module_name
INTEGER module_id
module_config
INTEGER module_id
VARCHAR config_name
VARCHAR config_value
config item names and values are rather arbitrary
Suppose you subscribe to a public email list that offers support on a
free open source database, and you see an email where someone doesn't
really provide nearly enough information to answer, what would you do?
What is the algorithm you are trying to implement to get the query-output?
Roy
Hi,
One way of doing it would be:
select a.tolerance, a.Cycles as PartA, b.Cycles as PartB, c.Cycles as PartC
from t as a, t as b, t as c where a.tolerance=b.tolerance and
a.tolerance=c.tolerance and a.PartName='A' and b.PartName='B' and
c.PartName='C';
Jacek
-Original Message-
Roy Harrell [EMAIL PROTECTED] wrote on 10/04/2005
03:15:33 PM:
Suppose I have a simple table as follows:
PartName Tolerance Cycles
A 1 10
A 2 11
A 3 13
A 4 15
A 5 18
B 1 12
B 2 14
B 3 16
B 4
Jacek,
Your method would only work so long as each PartA, PartB, and PartC all
have the same tolerance numbers. if PartA and PartB had a tolerance of 20
but PartC didn't, your query would not show just the A and B tolerances.
In fact, it wouldn't show a line for Tolerance 20 at all.
The only
; mysql@lists.mysql.com
Subject: RE: Query Question
Jacek,
Your method would only work so long as each PartA, PartB, and
PartC all
have the same tolerance numbers. if PartA and PartB had a
tolerance of 20
but PartC didn't, your query would not show just the A and B
tolerances
clearly need more input from Roy.
Jacek
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 04, 2005 12:56 PM
To: Becla, Jacek
Cc: Roy Harrell; mysql@lists.mysql.com
Subject: RE: Query Question
Jacek,
Your method would
Roy,
How do I set up a query whose output would look like this:
Tolerance PartA PartB PartC
1 10 12 6
2 11 14 7
3 13 16 7
4 15 16 8
5 18 17 10
One
Barry [EMAIL PROTECTED] wrote on 10/03/2005 04:38:48 PM:
I'm new to MySQL and am using version 4-1-12a on my development
machine locally this query works just fine:
SELECT * FROM actor
WHERE id IN
( SELECT id FROM episode_cast
WHERE episode_number =001)
but when
- Original Message -
From: [EMAIL PROTECTED]
To: Barry
Cc: mysql@lists.mysql.com
Sent: Monday, October 03, 2005 9:49 PM
Subject: Re: Query Trouble!
Barry [EMAIL PROTECTED] wrote on 10/03/2005 04:38:48 PM:
I'm new to MySQL and am using version 4-1-12a on my
Hello,
Perfect :-)
Thank you,
Hobbs.
Quoting Peter Brawley [EMAIL PROTECTED]:
Richard
I would like to display all messages which match both 5 and 7 in terms
of the parent_id, meaning messages 10 and 13 would be displayed.
SELECT f1.child
FROM foo AS f1
INNER JOIN foo AS f2 USING(child)
Hobbs, Richard wrote:
Hello,
I have two tables - one containing messages, and another containing links
between messages and other messages in a tree structure (much like a
threaded mailing list archiving thing). A single message can have
multiple parents though, meaning the links table can
Richard
I would like to display all messages which match both 5 and 7 in terms
of the parent_id, meaning messages 10 and 13 would be displayed.
SELECT f1.child
FROM foo AS f1
INNER JOIN foo AS f2 USING(child)
WHERE f1.parent=5 AND f2.parent=7;
PB
-
Hobbs, Richard wrote:
Hello,
I have
Hello.
My $0.05 :)
If you find out the unique queries (they often contain date and time),
add SQL_NO_CACHE to SELECT statement - this will decrease the number
of inserts of unique queries.
Brent Baisley [EMAIL PROTECTED] wrote:
You tripled the amount of memory assigned to the
You tripled the amount of memory assigned to the cache, but your
Qcache_lowmem_prunes value went up 10x and Qcache_not_cached went up
6x. I would say that you have enough queries that are unique, that
most of your queries are getting cached then flushed before the same
query comes up
a simple select like this (if you know the order of tables):
SELECT (tableb.valuea + + tableb.valueb + + tablea.valuec)
FROM `tableb`, `tablea` where (tableb.valuea + + tableb.valueb +
+ tablea.valuec) = XXX XX
N. Kavithashree wrote:
hello
i hv one query as explained
N. Kavithashree [EMAIL PROTECTED] wrote on 09/14/2005
02:07:01 AM:
hello
i hv one query as explained below :
Table FIRST contains
This is
What is this
how
whom
color
white
Table SECOND contains
WHOM
THIS IS
WHAT
WHITE AND BLACK
i want the result as
This is
what is
Hello.
Are you sure that the logging isn't enabled in a different way.
For possible ways to enable logs see:
http://dev.mysql.com/doc/mysql/en/program-options.html
I think I must have had my head screwed on the wrong way round
when I was looking at the log file. I've deleted it now, but
I
Hello.
host when I have hostnames in the 'user' table. It will only
work with ip addresses. (This is where I came in originally).
Are you sure that you're running MySQL without --skip-name-resolve? See:
http://dev.mysql.com/doc/mysql/en/dns.html
I think I must have had my
Hello.
Are you sure that the logging isn't enabled in a different way.
For possible ways to enable logs see:
http://dev.mysql.com/doc/mysql/en/program-options.html
What version of MySQL are you running?
T. Horsnell [EMAIL PROTECTED] wrote:
Someone kindly replied to my log query
Trouble is, I dont start mysqld with the --log= option,
and yet it still insists on logging. How do I disable this?
(My current method is to use --log=/dev/null but I'm sure
this cant be the best way). I cant find any evidence of
a my.cnf anywhere, so I dont think it is due to an unexpected
Balazs Bagi [EMAIL PROTECTED] wrote on 08/24/2005 05:10:55 AM:
Hi there, I'm kind of new to joining two tables. Please bear with me
with this simple example. I have three tables.
Guests is a list of guests that are coming to the party. The primary
key of this table is 'id' and the
[snip]
I need some help with a MySQL query using PHP can someone help please?
[/snip]
First of all, do not threadjack. Start a new thread to the list with
your request.
Second, sending a general request like this will likely get you flamed.
Usually I am pretty good at flaming folks, but today I
Rakki wrote:
Hi,
Can anybody give me the optimized query for full-text searching?
User will be entering one or more words in a text box and I wanted to
display the records which has atleast one word of the user input.
Assume that my table has two fields USER and DESCRIPTION.
I wanted
Hello.
Something like this:
SELECT INET_NTOA((INET_ATON(odip)8)8) AS mask
, COUNT(odip)
FROM a
GROUP BY mask;
You can merge results from both tables using UNION. See more about
INET_xxx functions at:
On 16/08/2005, Octavian Rasnita wrote:
I want to create a query that selects the diference between the value
of a field from the current record and the value of the same field
from the previous record.
How do you define current record and previous record?
(relational databases are not
16, 2005 2:45 PM
Subject: Re: query
On 16/08/2005, Octavian Rasnita wrote:
I want to create a query that selects the diference between the value
of a field from the current record and the value of the same field
from the previous record.
How do you define current record and previous
record is that that contains the yesterday
date.
Thank you.
Teddy
- Original Message -
From: Felix Geerinckx [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, August 16, 2005 2:45 PM
Subject: Re: query
On 16/08/2005, Octavian Rasnita wrote:
I want to create
)
-Original Message-
From: Octavian Rasnita [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 16, 2005 7:51 AM
To: mysql@lists.mysql.com; Felix Geerinckx
Subject: Re: query
Hi,
I define the previous record by date. For each record corresponds a date
which is unique and the previous record
Jason Chan wrote:
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in
I am using mysql 4.0.25 with no subquery support : (
Jason Chan [EMAIL PROTECTED] ¦b¶l¥ó news:[EMAIL PROTECTED]
¤¤¼¶¼g...
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem'
and sg.Grade = 'A'
The key here is using the aliases for the tables. The linking of the tables
comes in the s.StudentID = sg.StudentID statement.
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1
Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem'
and sg.Grade = 'A'
Take a close look at the WHERE clause: sg.Subject = 'Maths' and
sg.Subject = 'Chem' will never return a result - how could the
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1
Jason Chan wrote:
I am using mysql 4.0.25 with no subquery support : (
Jason Chan [EMAIL PROTECTED] ¦b¶l¥ó news:[EMAIL PROTECTED]
¤¤¼¶¼g...
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
Alternatively, use an IN list for the subject, then use a HAVING clause
to limit the results to students with 2 matching rows, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem')
AND
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1Maths
Arno Coetzee wrote:
Jason Chan wrote:
I have a student Table and a SubjectGrade table
snip
I want to find out students who have got A in both Maths and Chem
How the SQL look like?
select s.StudentID , s.StudentName
from Student as s , SubjectGrade as sj
where s.studentID = sj.studentID
I havn't write my schema clearly , (StudentID, Subject) is the key of
SubjectGrade
Philippe Poelvoorde [EMAIL PROTECTED] ???
news:[EMAIL PROTECTED] ???...
Alternatively, use an IN list for the subject, then use a HAVING clause
to limit the results to students with 2 matching rows, like this:
So i have to write 3 join if I have 3 conditions and so on, right?
Scott Noyes [EMAIL PROTECTED] ???
news:[EMAIL PROTECTED] ???...
Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject =
'Chem'
and sg.Grade =
SELECT s.StudentID, StudentName
FROM Student s
JOIN StudentGrade sg1 USING (StudentID)
JOIN StudentGrade sg2 USING (StudentID)
WHERE
sg1.Subject = 'Maths' AND sg1.Grade = 'A'
AND sg2.Subject = 'Chem' AND sg2.Grade = 'A'
So i have to write 3 join if I have 3 conditions and so on,
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
Thanks Scott!
Scott Noyes [EMAIL PROTECTED] ???
news:[EMAIL PROTECTED] ???...
SELECT s.StudentID, StudentName
FROM Student s
JOIN StudentGrade sg1 USING (StudentID)
JOIN StudentGrade sg2 USING (StudentID)
WHERE
sg1.Subject = 'Maths' AND sg1.Grade = 'A'
AND sg2.Subject = 'Chem' AND
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Sebastian [EMAIL PROTECTED] wrote:
I have two tables: news and comments.
i want to count the number of comments for
--- Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Isn't the previous query going to update the
news.comments with the last
Hello.
No, it isn't. Here is the test case:
create table comments(id int auto_increment,newsid int,primary key(id));
create table news(id int,comments int);
insert into news(id) values('1'),(2),(3),(4),(5);
insert into comments(newsid) values(1),(1),(2),(4),(4),(4);
update news set
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar query last night, but i didnt use group by.
I have question, it it better to use
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005 01:19:32 PM:
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
Sebastian wrote:
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Nuno Pereira [EMAIL PROTECTED] wrote on 08/08/2005 01:49:44 PM:
Sebastian wrote:
Nuno Pereira wrote:
[EMAIL PROTECTED] wrote:
Sebastian [EMAIL PROTECTED] wrote on 08/08/2005
10:53:55 AM:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET
Sebastian wrote:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
( SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar query last night, but i didnt use group by.
I have question, is it better to use
m i l e s [EMAIL PROTECTED] wrote on 08/08/2005 03:10:21 PM:
Hi,
IS the following query counting cumulative (see below **) pHITS or is
it counting individual counts for each user for a particular day. Im
not skilled enough to answer this question myself. My instinct says
that its
Miles,
IS the following query counting cumulative (see below **) pHITS or is
it counting individual counts for each user for a particular day. Im
not skilled enough to answer this question myself. My instinct says
that its counting cumulative values and NOT individual counts for each
Michael Stassen wrote:
Not exactly. They aren't the same. COUNT(id) counts distinct values of
id, while COUNT(*) simply counts rows. [snip]
Actually, COUNT(id) counts non-NULL values of id. COUNT(DISTINCT id)
would count distinct values.
Shawn,
Took me a bit to digest what you were sayin but if I get it the way
you splain'd it
then the following should work:
++
SELECT
cmc_search_members.PropertyName AS pNAME,
cmc_search_members.PropertyEmail AS pEMAIL,
Jasper Bryant-Greene wrote:
Michael Stassen wrote:
Not exactly. They aren't the same. COUNT(id) counts distinct values
of id, while COUNT(*) simply counts rows. [snip]
Actually, COUNT(id) counts non-NULL values of id. COUNT(DISTINCT id)
would count distinct values.
m i l e s [EMAIL PROTECTED] wrote on 08/08/2005 05:05:46 PM:
Shawn,
Took me a bit to digest what you were sayin but if I get it the way
you splain'd it
then the following should work:
++
SELECT
cmc_search_members.PropertyName
Is this what you're after?
SELECT
COUNT(*) AS members,
SUM(IF(payment_type = 'creditcard', 1, 0)) AS pay_by_card
FROM
members
JOIN payments USING (memno)
GROUP BY group;
On 8/5/05, Russell Horn [EMAIL PROTECTED] wrote:
I'm having difficulty writing a query as follows. I'm unsure
Russell Horn wrote:
I'm having difficulty writing a query as follows. I'm unsure if I need a
subquery, a union or if this isn't actually possible without using
temporary tables.
I have two tables for members.
Table 1 (members) Table 2 (payments)
+--++
Thanks for the two incredibly fast responses, they were perfect -
problem solved.
Russell.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Eddie wrote:
How can I join two tables looking like this?
Table 1:
++---+---+
| Id | Name | Score |
++---+---+
Table 2:
++---+---+---+
| Id | Name | Score | Info |
++---+---+---+
To get output table like this:
Table 2:
[snip]
I have this query which gets executed often, but when I saw this today I
panicked. Any suggestions to why this took so long?
mysql SELECT t2.id, t2.bdate, t2.level FROM bvolset AS t1 JOIN bvolset
AS
t2 ON t2.bdatet1.bdate AND t2.levelt1.level WHERE t1.id=30 ORDER BY
bdate DESC LIMIT 1;
[EMAIL PROTECTED] wrote on 08/01/2005 09:13:46 AM:
I have this query which gets executed often, but when I saw this today I
panicked. Any suggestions to why this took so long?
mysql SELECT t2.id, t2.bdate, t2.level FROM bvolset AS t1 JOIN bvolset
AS
t2 ON t2.bdatet1.bdate AND
Jay Blanchard [EMAIL PROTECTED] wrote on 08/01/2005
09:20:59 AM:
[snip]
I have this query which gets executed often, but when I saw this today I
panicked. Any suggestions to why this took so long?
mysql SELECT t2.id, t2.bdate, t2.level FROM bvolset AS t1 JOIN bvolset
AS
t2 ON
[snip]
Proper indexing will solve your problem. Index bdate and level, that
should speed things up considerably.
From the looks of things, bdate and level are *already* indexed. Or, did
you want him to create a multi-column index on bdate and level? You
weren't very clear in your suggestion.
On Mon, 1 Aug 2005, Jay Blanchard wrote:
CREATE TABLE bvolset (
id int(11) NOT NULL auto_increment,
bdate datetime default NULL,
level int(11) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY bdate (bdate),
KEY level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[/snip]
I have tables that are over 7 million records and I originally had the
same issue, however if you will create indexes in those tables, on the
columns that you will be using for your queries this will GREATLY speed up
your queries.
I am sure that there is a more concise way to state how you should
Christos Andronis wrote:
Hi all,
we are trying to run the following query on a table that contains over 600 million rows:
'ALTER TABLE `typed_strengths` CHANGE `entity1_id` `entity1_id` int(10)
UNSIGNED DEFAULT NULL FIRST'
The query takes ages to run (has been running for over 10 hours
Not knowing what the ALTER TABLE is changing, I can't really say. Could
you send the table structure (as it was before the ALTER TABLE)?
Remember that MySQL is actually changing the data for every row, and
potentially rebuilding indexes as well, so it has a lot of work to do
from that single
501 - 600 of 1474 matches
Mail list logo
