Hi!
Andrew == Andrew Aksyonoff [EMAIL PROTECTED] writes:
Andrew Hello Sergey,
Andrew Monday, September 15, 2008, 10:41:31 PM, you wrote:
in MySQL but in general case it can't assume any order and will have
to re-sort the sub-select result by outer GROUP BY instead of inner
ORDER BY. If that
Hello Michael,
Tuesday, September 30, 2008, 8:00:36 PM, you wrote:
Andrew However if the specific sorting algorithm is not stable it *might*
MW It's not stable; MySQL is using several different technics to
MW calculate GROUP BY and may thus return the rows in any order within
MW the group by.
SELECT DISTINCT can be kind of slow if there are many result values,
specifically if those result values include large VARCHARs. Furthermore,
some database engines cannot support a SELECT DISTINCT if any LOBs are
included in the result values.
I'm trying to find a general way to optimize SELECT
from an index.
select distinct date_id from data_table;
# returns 89 rows in 23 seconds
- simple index on date_id with 2 other indices
- just under 40,000,000 rows in table
- index file is 730 MB
EXPLAIN SELECT gives the following:
type = index
key = date_id
rows = 39726908
extra = using index
I have a query that takes a long time even though it is only examining the
index file. Is this normal? I would have thought that MySQL would be much
quicker to get the unique values from an index.
select distinct date_id from data_table;
# returns 89 rows in 23 seconds
- simple index
]
To: mysql@lists.mysql.com
Sent: Wednesday, January 04, 2006 8:28 PM
Subject: SELECT DISTINCT uses index but is still slow
I have a query that takes a long time even though it is only examining the
index file. Is this normal? I would have thought that MySQL would be much
quicker to get the unique
Hello list,
I'm getting the distinct 'params' columns from a table with this query
SELECT DISTINCT params FROM table;
but this gets the rows with distinct 'params' cols, but I want to know
from which row each params correspond, like this (which is not correct)
SELECT id, DISTINCT params
Nuno Pereira [EMAIL PROTECTED] wrote on 08/05/2005 02:04:35 PM:
Hello list,
I'm getting the distinct 'params' columns from a table with this query
SELECT DISTINCT params FROM table;
but this gets the rows with distinct 'params' cols, but I want to know
from which row each params
Here's one (not very clean, but it works) way to do it:
SELECT id, params FROM table GROUP BY params;
The trouble is, how do you know which id should come with it? If you table is
id param
1 1
2 1
should the query return
1, 1
or
2, 1
?
--
MySQL General Mailing List
For list archives:
[EMAIL PROTECTED] wrote:
Depending on what version server you are running, the GROUP_CONCAT()
function may be an option for you:
SELECT params, GROUP_CONCAT(ID)
FROM table
GROUP BY params;
I'm using 4.1.10a-standard-log Server version, and this is just what I
wanted.
Thanks.
--
Nuno
Scott Noyes wrote:
Here's one (not very clean, but it works) way to do it:
SELECT id, params FROM table GROUP BY params;
The trouble is, how do you know which id should come with it? If you table is
id param
1 1
2 1
should the query return
1, 1
or
2, 1
?
This is not really what I
SELECT DISTINCT ROW(A,B) from a;
While I am on a roll (of sorts) can I ask if the above syntax should be
valid or not?
I have a feeling it should be valid (but isn't - it causes an error).
Is this the correct behaviour?
--
MySQL General Mailing List
For list archives: http
Dan Bolser wrote:
SELECT DISTINCT ROW(A,B) from a;
While I am on a roll (of sorts) can I ask if the above syntax should be
valid or not?
If you mean the exact syntax above, I think not... it looks like ROW()
is a function taking two parameters in this case... what does the
function return
On Sat, 7 May 2005, Roger Baklund wrote:
Dan Bolser wrote:
SELECT DISTINCT ROW(A,B) from a;
While I am on a roll (of sorts) can I ask if the above syntax should be
valid or not?
If you mean the exact syntax above, I think not... it looks like ROW()
is a function taking two parameters
People, including me, often expect the wrong thing from SELECT DISTINCT,
May suggest you do the following:
Create table temp_tb_spots as
Select * from tb_spots where aired_station = '??'
;
select distinct Date from temp_tb_spots group
;
In a different window run
Select Date from
I have a large database that I am trying to run a SELECT DISTINCT
across.
SELECT DISTINCT Date FROM tb_spots WHERE aired_station = '??'
However the results I am getting from this query do not match up with
the data on the database, instead there are large gaps.
Is there any know problem
-4185 Fax
http://www.meitech.com/
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 26, 2005 12:08 AM
To: Frederic Wenzel
Cc: Gustafson, Tim; mysql@lists.mysql.com
Subject: Re: Problems with select distinct
Frederic Wenzel wrote:
On Tue, 25
Hello
I am trying to run the following query on my server:
select Documents.ID,
Name
from Documents,
DocumentFiles,
DownloadLog
where Documents.ID = DocumentFiles.Document and
DocumentFiles.ID = DownloadLog.DocumentFile
order by DownloadLog.AddedOn desc
limit 5
I suspect mysql is doing the DISTINCT before the ORDER BY. One of the
reasons I avoid DISTINCT when possible. Try:
SELECT Documents.ID, Name
FROM Documents, DocumentFiles, DownloadLog
WHERE Documents.ID = DocumentFiles.Document
AND DocumentFiles.ID = DownloadLog.DocumentFile
GROUP
On Tue, 25 Jan 2005 08:44:45 -0500, Michael Stassen
[EMAIL PROTECTED] wrote:
I suspect mysql is doing the DISTINCT before the ORDER BY. One of the
reasons I avoid DISTINCT when possible.
Isn't this supposed to be correct? Ordering has to take place as the
very last operation, after any
Frederic Wenzel wrote:
On Tue, 25 Jan 2005 08:44:45 -0500, Michael Stassen
[EMAIL PROTECTED] wrote:
I suspect mysql is doing the DISTINCT before the ORDER BY. One of the
reasons I avoid DISTINCT when possible.
Isn't this supposed to be correct? Ordering has to take place as the
very last
Answer to my query select distinct http://lists.mysql.com/mysql/175839
http://lists.mysql.com/mysql/175839
To select properly only one time one element of a column in a list (here the
journal name) I have to add a selection on the end of the name that it is
not a space or a return !
Both
I have a web application using MySQL 3.2x server. In the application
theres an HTML form used for searching an ISAM table of about 500k
records. To populate select (drop down) fields in the form I do a couple
of SELECT DISTINCT queries against this table.
What's the best indexing strategy
records. To populate select (drop down) fields in the form I do a
couple of SELECT DISTINCT queries against this table.
What's the best indexing strategy to speed up these queries? I've added
indexes for both of the fields on which I do a SELECT DISTINCT. It
seems to have helped a little
Pretty straightforward. The other query is identical, except that the
column is of type INT. It executes a little faster, as would problably be
expected.
SELECT DISTINCT directory
FROM pagestats
ORDER BY directory
+---+---+---+---+-++
| table
Using the request :
SELECT DISTINCT journal
FROM reference
WHERE TYPE = 'article' AND etat = 'published' AND journal = 'Physical Review
Letters'
I get two type of answers
One with 'Physical Review Letters'
(WHERE `journal` = 'Physical Review Letters' )
the other with 'Physical Review
distinct
values in there, and a select distinct over an indexed column doesn`t
need to do a full scan.
An open-coded loop
val = db.Do(select min(IP) from test)
while (val) {
process(val)
val = db.Do(select min(IP) from test where IP '$val')
}
runs almost instantly. MySQL should be able
Matthias,
I think that MySQL is doing what would be expected, namely an index scan
which reads entire index on IP and selects distinct values of IP. Whatever
you do, it's going to read 10,991,123 of something and select distinct
values. The use of the index saves a possible sort and allows
',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test
default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
Ideas, anybody?
[/snip]
Because IP is not an INDEX, only a KEY, just
a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test;
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type
Hi,
gerald_clark:
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
Your second key is redundant.
True (there's a UNIQUE missing in there).
Deleting the first key, however, doesn't change anything.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL
I had a similar problem, but my criteria for selecting the
value of f1 was different; it's a date field and I wanted
only the rows with the most recent date value in that field,
so only the latest of otherwise identical entries got inserted.
I ended up doing something like this:
create temporary
How about
INSERT INTO original_table
SELECT MAX(f1), f2, f3 FROM new_table GROUP BY f2, f3;
Michael
Stephen E. Bacher wrote:
I had a similar problem, but my criteria for selecting the
value of f1 was different; it's a date field and I wanted
only the rows with the most recent date value in
say I'm selecting distinct (non-duplicate) rows for insertion,
insert into original_table select distinct * from new_table
these tables have 3 fields/row. Per the above code all 3 fields are
evaluated by distict * .
But my question is: I want to ignore field1, therefore I only want to
test
,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM:
say I'm selecting distinct (non-duplicate) rows for insertion,
insert into original_table select distinct * from new_table
these tables have 3 fields/row. Per
,
insert into original_table select distinct * from new_table
these tables have 3 fields/row. Per the above code all 3 fields are
evaluated by distict * .
But my question is: I want to ignore field1, therefore I only want to
test if any rows have field2 *and* field3 as dups - that would
as well?
Nope. Just 3 cols. Thanks
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM:
say I'm selecting distinct (non-duplicate) rows for insertion,
insert into original_table select
On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said:
Let me see if I can explain it a little betterIf you need to move all
3 columns to the new table but you only want *1* row where f2 and f3 have
a unique combination of values, how do you want to choose *which* value
of
f1 to
You were perfectly clear. We understand that you only want to test f2 and
f3 for uniqueness. The question is, which of the possible values of f1 do
you want to get. Do you see? For a particular unique f2, f3 combination,
there may be multiple f1 values. How should we choose which one to
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen
[EMAIL PROTECTED] said:
You were perfectly clear. We understand that you only want to test f2
and f3 for uniqueness. The question is, which of the possible values
of f1 do you want to get. Do you see? For a particular unique f2, f3
Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target
table, leave it out of the SELECT, and let it auto-generate IDs. Something
like this:
INSERT INTO original_table (f2, f3)
SELECT DISTINCT f2, f3 FROM new_table;
I did that in the same order as your original message
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen
[EMAIL PROTECTED] said:
You were perfectly clear. We understand that you only want to test f2
and f3 for uniqueness. The question is, which of the possible values
of f1 do you want to get. Do you see? For a particular unique f2, f3
like this:
INSERT INTO original_table (f2, f3)
SELECT DISTINCT f2, f3 FROM new_table;
I did that in the same order as your original message, though I'd have
expected original_table and new_table to be swapped, based on their
names.
See the manual http://dev.mysql.com/doc/mysql/en
:
|
| Fax to:
|
| Subject: SELECT DISTINCT + ORDER BY confusion
of products. however, if i use SELECT DISTINCT it applies that
BEFORE it does the sort, so i don't get only the most recent products.
what i actually get seems to be pretty random.
SELECT DISTINCT p.id,p.title
FROM product p
join e_prod ep on ep.product=p.id
join story s on s.id = ep.story
Have you tried using a group by clause? Group by title
-Original Message-
From: news
To: [EMAIL PROTECTED]
Sent: 7/9/04 3:08 PM
Subject: SELECT DISTINCT + ORDER BY confusion
I've got a product story setup where there can be multiple stories of
a given type for any product. I want
Victor Pendleton wrote:
Have you tried using a group by clause? Group by title
same problem - the group by happens before the order by and you get
essentially random results.
-Original Message-
From: news
To: [EMAIL PROTECTED]
Sent: 7/9/04 3:08 PM
Subject: SELECT DISTINCT + ORDER
If you are usign 4.1 you could try:
SELECT DISTINCT d, title
FROM
(select p.id, p.title
from product p
join e_prod ep on ep.product=p.id
join story s on s.id = ep.story and s.status = 9 and
s.type = 14
where p.platform_id = 5 and p.genre_id = 23282
order by s.post_date desc
)
limit 10
From: Paul DuBois
At 17:50 -0500 5/16/04, Paul DuBois wrote:
Not a huge difference, I guess. But I suppose if a query that
uses one or the other of these expressions processes a large number
of rows, it might pay to run some comparative testing.
Another interesting point is whether one
Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me an empty array. What am I doing wrong?
TNX
--
MySQL General Mailing List
For list archives: http
16, 2004 11:36 AM
To: [EMAIL PROTECTED]
Subject: Select distinct year from unix timestamp
Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me
From: T. H. Grejc
Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me an empty array. What am I doing wrong?
TNX
I think you need
At 22:27 +0200 5/16/04, John Fawcett wrote:
From: T. H. Grejc
Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me an empty array. What am I doing wrong
John Fawcett wrote:
From: T. H. Grejc
Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me an empty array. What am I doing wrong?
TNX
I think you need
Paul DuBois wrote:
At 22:27 +0200 5/16/04, John Fawcett wrote:
From: T. H. Grejc
Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me an empty array. What
From: T. H. Grejc
How can I add more fields to query. If I write:
SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM
table_name ORDER BY created DESC
I loose distinction (all dates are displayed).
TNX
I don't think distinction is lost. All the rows should still be distinct
From: Paul DuBois At 22:27 +0200 5/16/04,
John Fawcett wrote:
Year does not operate on a unix timestamp.
Sure it does:
mysql select t, year(t) from tsdemo1;
++-+
| t | year(t) |
++-+
| 20010822133241 |2001 |
|
John Fawcett wrote:
From: T. H. Grejc
How can I add more fields to query. If I write:
SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM
table_name ORDER BY created DESC
I loose distinction (all dates are displayed).
TNX
I don't think distinction is lost. All the rows should still
At 0:25 +0200 5/17/04, John Fawcett wrote:
From: Paul DuBois At 22:27 +0200 5/16/04,
John Fawcett wrote:
Year does not operate on a unix timestamp.
Sure it does:
mysql select t, year(t) from tsdemo1;
++-+
| t | year(t) |
++-+
|
From: Paul DuBois
You're right. You'd have to apply YEAR() to
FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
and you can avoid YEAR() altogether by using a
format string. in FROM_UNIXTIME()
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
From: T. H. Grejc
I'm creating news archive and it should be sorted by months:
January 2004 (news count is 56)
February 2004 (48)
...
So you need to use GROUP BY and COUNT.
The format is like this:
select monthandyear,count(othercolumn) from t
group by monthandyear
in your case
At 0:38 +0200 5/17/04, John Fawcett wrote:
From: Paul DuBois
You're right. You'd have to apply YEAR() to
FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
and you can avoid YEAR() altogether by using a
format string. in FROM_UNIXTIME()
Right again. :-)
--
Paul DuBois, MySQL Documentation Team
Madison,
At 17:50 -0500 5/16/04, Paul DuBois wrote:
At 0:38 +0200 5/17/04, John Fawcett wrote:
From: Paul DuBois
You're right. You'd have to apply YEAR() to
FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
and you can avoid YEAR() altogether by using a
format string. in FROM_UNIXTIME()
Right again. :-)
I was curious
Hello List,
Please forgive this rather lengthy post. I thought I had something worked
out to answer Rob's question but when I put it to the test I found what MAY
be a bug in 4.1.1a-alpha-nt-log. Here is what I did.
I created two tables, tablea and tableb. Here are their defs:
mysql show create
I've solved the problem starting the server using the
option --character-set=dos.
The side effect is that searches become
accent-sensitive.
Best wishes.
--- Ricardo [EMAIL PROTECTED] escreveu: I have a
problem with SELECT DISTINCT if the target
field contains special characters.
Example
order. What is the collation in MySQL?
http://dev.mysql.com/doc/mysql/en/Charset-server.html
That's for 4.1 and up. There isn't such a concept of collation in 4.0.x.
Ricardo, you might try SELECT DISTINCT BINARY col_name
rather than SELECT DISTINCT col_name.
--
Paul DuBois, MySQL Documentation
I have a problem with SELECT DISTINCT if the target
field contains special characters.
Example:
select MyField from MyTable
+--+
| MyField |
+--+
| fá |
| Fá |
| fa |
| Fa |
| fâ |
| Fâ |
| fã |
| Fã |
+--+
select
Try
SELECT DISTINCT BINARY(col)
...
Or declare the column as binary
-Original Message-
From: Ricardo
To: [EMAIL PROTECTED]
Sent: 4/28/04 9:02 AM
Subject: SELECT DISTINCT returns an incorrect result with special characters
I have a problem with SELECT DISTINCT if the target
field
Thanks Mr. Pendleton.
But I cannot change the behaviour of the LIKE operator
and the ORDER BY clause, which are going to be
affected by both of your suggestions.
--- Victor Pendleton [EMAIL PROTECTED]
escreveu:
Try
SELECT DISTINCT BINARY(col)
...
Or declare the column as binary
Ricardo [EMAIL PROTECTED] wrote:
I have a problem with SELECT DISTINCT if the target
field contains special characters.
Example:
select MyField from MyTable
+--+
| MyField |
+--+
| f? |
| F? |
| fa |
| Fa |
| f? |
| F
Why can you not write
SELECT DISTINCT BINARY(col)
FROM table1
WHERE BINARY(col) LIKE 'criteria'
ORDER BY col
?
-Original Message-
From: Ricardo
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 4/28/04 9:33 AM
Subject: RE: SELECT DISTINCT returns an incorrect result with special char
acters
- The sort order gets incorrect:
SELECT DISTINCT BINARY(MyField)
FROM MyTable
WHERE MyField LIKE 'f%'
ORDER BY MyField
+-+
| BINARY(MyField) |
+-+
| fa |
| Fâ |
| fá |
| Fa |
| fã |
| Fá
latin1
--- Egor Egorov [EMAIL PROTECTED] escreveu:
What is the character set of the data? What is the
character set of MySQL server?
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net
http://www.ensita.net/
__ ___
It does not appear to respect the ascii values.
Should the order be?
fa
fá
fâ
fã
fa
fá
fâ
fã
-Original Message-
From: Ricardo
To: [EMAIL PROTECTED]
Sent: 4/28/04 10:05 AM
Subject: RE: SELECT DISTINCT returns an incorrect result with special char
acters
- The sort order gets incorrect
The order should be:
fa
Fa
fá
Fá
fâ
Fâ
fã
Fã
As I get in MS-SQL Server.
--- Victor Pendleton [EMAIL PROTECTED]
escreveu:
It does not appear to respect the ascii values.
Should the order be?
fa
fá
fâ
fã
fa
fá
fâ
fã
Ricardo wrote:
The order should be:
fa
Fa
fá
Fá
fâ
Fâ
fã
Fã
As I get in MS-SQL Server.
Which collation are you using in MS SQL Server? And in MySQL?
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
MySQL General Mailing List
For list archives:
Which collation are you using in MS SQL Server? And
in MySQL?
Jochem
MS SQL Server 8
Collation = Latin1_General_CI_AS
MySQL 4.0.16
character_set = latin1
__
Yahoo! Messenger - Fale com seus amigos online. Instale agora!
Ricardo wrote:
Which collation are you using in MS SQL Server? And
in MySQL?
MS SQL Server 8
Collation = Latin1_General_CI_AS
MySQL 4.0.16
character_set = latin1
The charset determines which characters can occur. The collation
determines the sort order. What is the collation in MySQL?
But I think the collation set only applies to MySQL
4.1, doesn't it? I'm using MySQL 4.0.16.
I found no character_set_server system variable.
Only character_set.
http://dev.mysql.com/doc/mysql/en/Charset-map.html
Thanks.
--- Jochem van Dieten [EMAIL PROTECTED]
escreveu: Ricardo wrote:
team commented on my previous post, and the
issue seems very serious, to the point I may start looking to
switching away from MySQL, so - please, please, shed some light on
this issue!!!
The problem is that the performance of SELECT DISTINCT... query
seems to depend on the order the results
!!!
The problem is that the performance of SELECT DISTINCT... query seems
to depend on the order the results are sorted, DESC being more than
10x slower than ASC (14.77 sec vs. 1.06 sec).
==
Here is a more detailed description:
The table has over 700,000 records
Hello,
The two columns of my table are name and city. I am trying to do a list which contains
only the different names. This can be done this by distinct keyword. But all the same
names which have different cities also have to be included in my list. So I can not do
this with a distinct
* Veysel Harun Sahin
The two columns of my table are name and city. I am trying to do
a list which contains only the different names. This can be done
this by distinct keyword. But all the same names which have
different cities also have to be included in my list. So I can
not do this with a
:) I have solved the problem.
Thanks.
[EMAIL PROTECTED] (Veysel Harun Sahin) wrote:
Hello,
The two columns of my table are name and city. I am trying to do a list which
contains only the different names. This can be done this by distinct keyword. But all
the same names which have different
select distinct(col1,col2) should work.. Group by most certainly will... Select
col1,col2 from table group by col1, col2.. Same thing
Hello,
The two columns of my table are name and city. I am trying to
do a list which contains only the different names. This can
be done this by distinct
: select distinct from two columns
Hello,
The two columns of my table are name and city. I am trying to do a list
which contains only the different names. This can be done this by distinct
keyword. But all the same names which have different cities also have to be
included in my list. So I can
Alan Dickinson [EMAIL PROTECTED] wrote:
I've got a query that looks like this..
SELECT Foot_Id, Dir_Uni, Part_Suffix, Part_Number, Appln_No, Description,
Product_Ref, Vehicle_Ref, Part_Prompt, Part_Description, Foot_Id,
Qualifier, Years
FROM
I've got a query that looks like this..
SELECT Foot_Id, Dir_Uni, Part_Suffix, Part_Number, Appln_No, Description,
Product_Ref, Vehicle_Ref, Part_Prompt, Part_Description, Foot_Id,
Qualifier, Years
FROM application_parts
WHERE (('$id' = Appln_No) and
.
Now I have to show the thumbnails of this products but only for one color.
So my query should look something like this:
SELECT DISTINCT(product_number) FROM products.
Now this query will return all the different product numbers i.e. but it
will also return the different colors
SELECT DISTINCT(LEFT (product_number,5)) FROM products.
http://www.mysql.com/doc/en/String_functions.html
hth
Jeff
Personal
Thank you!!!
It works.
Hector
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 13, 2003 1:44 PM
To: Personal
Cc: [EMAIL PROTECTED]
Subject: Re: SELECT DISTINCT question
SELECT
Hey everyone,
I have a question about the speed of selecting distinct values on an
indexed column. I have a table with a five column primary key and 3,215,540
records. I want to select all of the distinct values of the first column in
the primary key. This column only has 549 distinct
Nathan Cassano wrote:
Hey everyone,
I have a question about the speed of selecting distinct values on an
indexed column. I have a table with a five column primary key and 3,215,540
records. I want to select all of the distinct values of the first column in
the primary key. This column only
when I have 2 fields in the primary key
- Original Message -
From: Nathan Cassano [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, September 15, 2003 5:07 PM
Subject: Select distinct speed on an indexed column
:
: Hey everyone,
: I have a question about the speed of selecting
mysql explain select distinct AccountLevelId from PostedZpdi;
++---+---+-+-+--+-+-
+
| table | type | possible_keys | key | key_len | ref | rows|
Extra
wrote:
mysql explain select distinct AccountLevelId from PostedZpdi;
++---+---+-+-+--+-+-
+
| table | type | possible_keys | key | key_len | ref | rows|
Extra
From: Haydies [mailto:[EMAIL PROTECTED]
Sent: Monday, September 15, 2003 11:19 AM
To: [EMAIL PROTECTED]
Subject: Re: Select distinct speed on an indexed column
Its a compound key, they are always slow. I would imagin you will need to
seriously redesign your database to speed that up
: '[EMAIL PROTECTED]'
Subject: Re: Select distinct speed on an indexed column
Since your primary key is used, maybe you should consider adding an
index on PostedZpdi field only.
You may also check 'key_buffer_size' value (show variables like 'key%').
Since your primary key is more than 200 MB big
Yeah, I have a similar box like yours. I copied the first column to a new
table with an index. I ran select distinct and the query took 6 seconds to
execute. This must have to do with the record length, because when I indexed
the origional table's first column the query was 1 minute 30 seconds
1 - 100 of 155 matches
Mail list logo