Maybe I'm wrong :)
On Tuesday, July 21, 2009, John Daisley wrote:
>
>
> On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote:
>
>> On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote:
>>
>> > MySQL is unable to use your index when you use IN and/or OR on yoru
>> > column.
>>
>> Is this really
On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote:
> On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote:
>
> > MySQL is unable to use your index when you use IN and/or OR on yoru
> > column.
>
> Is this really true?
No its not true! Try running OPTIMIZE TABLE on the affected table, t
The other index does have a way higher cardinality, but the query is
for 3 columns all of which are in the first index. I guess this is
just one of the situations where MySQL makes a wrong assessment.
On Jul 21, 2009, at 3:54 PM, Brent Baisley wrote:
Try doing a "SHOW INDEX FROM orders"
On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote:
MySQL is unable to use your index when you use IN and/or OR on yoru
column.
Is this really true?
I'm reading "High Performance MySQL 2nd ed." these days and
specifically got the impression that using IN will allow usage of the
index. Th
Try doing a "SHOW INDEX FROM orders" and look at the cardinality
column. These are the stats MySQL uses to determine which index to
use. Sometimes they aren't always update properly and you may need to
run ANALYZE on the table.
But, you can also tell MySQL to use the index you want.
SELECT * FROM
MySQL is unable to use your index when you use IN and/or OR on yoru column.
If the query is slow, you should switch to a union:
SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND
status_id =1
UNION
SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND
status_id =
Hi, I have a table "orders" with the columns
item_id INT FK items(id)
customer_id INT FK customers(id)
status_id TINYINT -- Between 1 and 4 always
ordered_at DATETIME
delivered_at DATETIME
There are indexes:
index_a: (item_id, customer_id, status_id)
index_b: (item_id, status_id,
>-Original Message-
>From: Rob Wultsch [mailto:[EMAIL PROTECTED]
>Sent: Monday, May 19, 2008 11:20 AM
>To: Jerry Schwartz
>Cc: Scott Haneda; mysql@lists.mysql.com
>Subject: Re: improving random record selection
>
>On Mon, May 19, 2008 at 7:24 AM, Jerry Schwartz
&g
On Mon, May 19, 2008 at 7:24 AM, Jerry Schwartz
<[EMAIL PROTECTED]> wrote:
> I might not understand what this is doing, but I think it will preferentially
> sample the ids that are at the end of a gap.
What don't you understand about the query or the way I described it?
> You say you want a "fla
>-Original Message-
>From: Rob Wultsch [mailto:[EMAIL PROTECTED]
>Sent: Saturday, May 17, 2008 6:47 PM
>To: Scott Haneda
>Cc: mysql@lists.mysql.com
>Subject: Re: improving random record selection
>
>On Sat, May 17, 2008 at 2:32 PM, Scott Haneda &l
>From: Scott Haneda [mailto:[EMAIL PROTECTED]
>Sent: Saturday, May 17, 2008 5:32 PM
>To: mysql@lists.mysql.com
>Subject: improving random record selection
>
>I posted this a month or so ago, and was helped a little, but I am now
>back.
>
>Currently I use select x, y, z f
On Sat, May 17, 2008 at 2:32 PM, Scott Haneda <[EMAIL PROTECTED]> wrote:
>$sql = "
>SELECT storage_path, image_md5, t.id
>FROM images AS t
>JOIN
>(SELECT CEIL(MAX(id)*RAND()) AS id FROM images) AS x
> ON (t.id >
I posted this a month or so ago, and was helped a little, but I am now
back.
Currently I use select x, y, z from images where (condition) order by
rand() limit 1;
As most know, it is slow, depending on the record set, and what I
compare it to, it can be from one order of magnitude slower,
> 63210 05 8921159 76 0 689
> 63210 06 8262226 145 0 784
> 63210 07 7708146 91 22 1337
>
> In every selection I need the column sum(pzt) and the 2 peak-values
> of sum(u1), sum(u2)
22 1337
In every selection I need the column sum(pzt) and the 2 peak-values
of sum(u1), sum(u2) and sum(u3). The other value should be replaced
by zero.
e.g. for the first line (summ(uu) changed to 0)
mnr kw sum(pzt)sum(uu) sum(u01)sum(u02)sum(u03)
63210
se
on the selection of the list box.
echo'';
$query = "SELECT column1 FROM table";
$result = mysql_query($query);
echo"";
echo"";
echo"";
echo"";
echo"Choose a Category:
";
t data in to columns base on the selection of
> the list box.
>
>
>
> Can you help me please?
>
>
> 1-I have created a while loop to populate the list box
> with the information of column1.
> 2-I need to update (insert data) in to column2,
> column3, colu
Can you help me please?
1-I have created a while loop to populate the list box with the information
of column1.
2-I need to update (insert data) in to column2, column3, column4 base
on the selection of the list box.
echo'';
$query = "SELECT col
Hello
Thank you so much. I Just increase the size of some buffers:
# The MySQL server
[mysqld]
default-character-set = cp1251
port= 3306
socket = /x/xx.sock
basedir = /x
datadir = /x/x
log-error = /x/error.log
log-slow-queries = /x/slow.log
pid-f
Hello.
> "Fulltext initialization"
Ok, now we exactly know that we should optimize the FULLTEXT part
of your query. I don't know the options which directly affects the speed
of the FULLTEXT searches, so I suggest you to play with key_buffer and
table structure. The ratio of Key_reads to Ke
Hello
GP> What state is SHOW PROCESSLIST reporting for this query?
"Fulltext initialization"
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/ http://ryxi.com/
http://gyxe.com/ http://gyxu.com/
http://xywe.com/ http://xyqe.com/
--
MySQL General Mailing List
For l
Hello.
At first, we should ensure that most time query is spending in the
ordering of the results. What state is SHOW PROCESSLIST reporting
for this query?
Michael Monashev <[EMAIL PROTECTED]> wrote:
> Hello
>
> GP> And MySQL uses a filesort algorithm.
>
> How to make the filesor
Hello
GP> And MySQL uses a filesort algorithm.
How to make the filesort faster? May be I have to increase size of
some buffers?
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/ http://ryxi.com/
http://gyxe.com/ http://gyxu.com/
http://xywe.com/ http://xyqe.com/
--
Privet.
> SELECT SQL_CACHE id, program_name, categor, subcategor, subsubcategor,
> subsubsubcategor, source_url, rating, short_description, long_description,
> when_modifed+0, author, searched_words, size_in_kb, licence, language,
> install, win95, win98, winme, winnt, win2000, winxp, wince
Hello
GP> Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'
GP> statements, amount of RAM, most problematic queries (use mysqldumpslow
GP> utility to find them) include tables' definitions (use SHOW CREATE
GP> TABLE).
SHOW VARIABLES;
back_log50
basedir /***
Privet!
Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'
statements, amount of RAM, most problematic queries (use mysqldumpslow
utility to find them) include tables' definitions (use SHOW CREATE
TABLE).
Michael Monashev <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I hav
Hello,
I have 200-300 kb slow log daily with fulltext queries only :-( All
queries using fulltext indexes. I use huge mysql cofig (huge.cfg).
What can I change in the mysql configuration for better performance?
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/ htt
Michael Monashev wrote:
> How to select 5 random rows from big table with WHERE clause?
Maybe something like this:
SELECT col1, col2 FROM table WHERE col3=123 AND (id_col=RAND() OR
id_col=RAND() OR id_col=RAND() OR id_col=RAND() OR id_col=RAND())
the problem is that there is the (low) probabi
Privet!
> But this query can return empty results, if we use it with WHERE:
Yes, it can. However, we're able to change the technique and obtain
one random record with WHERE condition in query. With several iterations
it is possible to get several random records.
First we should get the
Hello
GP> Similar questions have been asked before. For example:
GP> http://lists.mysql.com/mysql/184088
Thank you. I found something interesting:
SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history;
SELECT * FROM history WHERE id >= @rand_id LIMIT 1;
But this query can retur
Put a index on col3 and it will be faster. That's the only way as far as I
know.
--
Chris.
- Original Message -
From: "Michael Monashev" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, July 20, 2005 3:04 PM
Subject: random rows selection
Hello,
How to select 5
Hello.
Similar questions have been asked before. For example:
http://lists.mysql.com/mysql/184088
Search in archives at:
http://lists.mysql.com/mysql
Michael Monashev <[EMAIL PROTECTED]> wrote:
> Hello,
>
> How to select 5 random rows from big table with WHERE claus
Hello,
How to select 5 random rows from big table with WHERE clause?
This query very slow on 1 mln rows:
SELECT col1, col2 FROM table WHERE col3=123 ORDER BY RAND() LIMIT 5
Have you a faster one?
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/ http://ryxi.com/
http://g
You have not tried merge?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 19, 2005 5:46 AM
To: Peter J Milanese; mysql@lists.mysql.com
Subject: Re: Multitable selection
Hello
Now I use temporary table, but I can`t use SQL_CACHE in queries, which
Hello
GP> If you have faster solution (in several times) please introduce it. I've
GP> seen similar issues at archives at:
GP> http://lists.mysql.com/mysql
I find it in docs :-) :
(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B
Hello
Now I use temporary table, but I can`t use SQL_CACHE in queries, which
contain temporary table :-( In theory 90% queries can be cached.
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/ http://ryxi.com/
http://gyxe.com/ http://gyxu.com/
http://xywe.com/ http://xyqe.
Monashev
Sent: Jul 19, 2005 1:58 PM
Subject: Re: Multitable selection
Hello
BM> Are you looking to compare a few last records in each table or ??
BM> Can I get a little bit more details?
I have 10 similar tables. 3 years ago it was one big table. Now I
split it to 10 tables.
3 years ago I
Hello
BM> Are you looking to compare a few last records in each table or ??
BM> Can I get a little bit more details?
I have 10 similar tables. 3 years ago it was one big table. Now I
split it to 10 tables.
3 years ago I could write:
SELECT * from BigTable WHERE date_col>$some_date ORDER BY date
Privet!
> What is faster: UNION or temporary table or something else ?
Sometimes MERGE tables could be used, but the speed difference between
UNION solution and MERGE is rather low. The temporary table for sorting
(ORDER clause causes this) should be created in all cases. So, if you
have
selection
Hello,
I have 10 tables:
table0: id, description, date
table2: id, description, date
...
table9: id, description, date
Every table has 1 000 000 rows.
How can I select all rows from this 10 tables ordered by date?
What is faster: UNION or temporary table or something else ?
mysql 4.1
Hello,
I have 10 tables:
table0: id, description, date
table2: id, description, date
...
table9: id, description, date
Every table has 1 000 000 rows.
How can I select all rows from this 10 tables ordered by date?
What is faster: UNION or temporary table or something else ?
mysql 4.1
Sincerely
> A31 1 5
>
> I would like to create a selection, which contains only samples,
containing
> a number of a species_a_ species_b or species_c. It should be like this:
>
> Sample A1: 5 species_b
> Sample A2: 3 species_c
> Sample A3: 1 speci
would like to create a selection, which contains only samples, containing
a number of a species_a_ species_b or species_c. It should be like this:
Sample A1: 5 species_b
Sample A2: 3 species_c
Sample A3: 1 species_a, 1 species_b and 1 species_c
With the "select command" I can't do
N. Kavithashree wrote:
when i retrieve records using follwg query :
select * from table where country='United States' and code='US';
Is the value if the country field "United States"? Or is it "United
States of America" or similar?
But if i give :s
select * from table where country like 'United S
hello,
i hv created a table with 11 fields. two of them where i m getting problem
are country name and the country code
all other fields work properly during selection except these two
fileds are : (country varchar(30),code char(2));
when i retrieve records using follwg query :
select * from
Michael/Wesley,
Thanks for your help. You got me going in the right direction!
Thanks again!!!
Mike
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 7:37 PM
To: Mike Koponick
Cc: [EMAIL PROTECTED]
Subject: Re: Auto Date selection and
Mike Koponick wrote:
Hello all,
I would like to be able to select the certain dates within my script.
select created_date, status, user, comment1, comment7, comment8, action
from users WHERE customerid = 'Customer' AND created_date BETWEEN
'2004-05-31' AND '2004-07-01' ORDER BY created_date, sta
I can help easily enough on formatting the dates...
The DATE_FORMAT( date, format ) function is what you want:
DATE_FORMAT( date, "%m/%d/%Y" )
Wes
On Jun 30, 2004, at 9:40 PM, Mike Koponick wrote:
Hello all,
I would like to be able to select the certain dates within my script.
select created_date,
Hello all,
I would like to be able to select the certain dates within my script.
select created_date, status, user, comment1, comment7, comment8, action
from users WHERE customerid = 'Customer' AND created_date BETWEEN
'2004-05-31' AND '2004-07-01' ORDER BY created_date, status into outfile
'te
cc:
Fax to:
06/28/2004 02:43 Subject: Re: Duplicate selection
Scott Haneda wrote:
So, I need to basically:
SELECT iamge_title, image_filename, image_url, image_width, image_height
from images where user_id = '123';
Then, take that result and insert all those rows in the same table, setting
type='2' and added=NOW()
I think I need to do a subselect, the docs ar
on 06/27/2004 11:31 PM, Scott Haneda at [EMAIL PROTECTED] wrote:
> So, I need to basically:
> SELECT iamge_title, image_filename, image_url, image_width, image_height
> from images where user_id = '123';
>
> Then, take that result and insert all those rows in the same table, setting
> type='2' an
4.0.18-standard
I need to duplicate a select of records, all in the same table, but also
make a change to one field.
Here is one table I need to do this on
++---+--+-++
| Field | Type | Null | Key | Default|
+--
"Marco Bresciani" <[EMAIL PROTECTED]> wrote:
> Egor Egorov <[EMAIL PROTECTED]> ha scritto:
>
>>You can't do it only with MySQL. Retrieve table names from List table:
>> SELECT CONCAT("FX", Name) FROM List;
>>
>>and then use programming language to construct a query.
>
> Thank you... I've su
Egor Egorov <[EMAIL PROTECTED]> ha scritto:
>You can't do it only with MySQL. Retrieve table names from List table:
> SELECT CONCAT("FX", Name) FROM List;
>
>and then use programming language to construct a query.
Thank you... I've supposed it...
What about using UNION? Something like:
SEL
"Marco Bresciani" <[EMAIL PROTECTED]> wrote:
> Hello all,
> I have a little problem to solve.
> I have a "List" table that lists the tables contained in the same DB. This
> table has a "Name" filed that contains such names.
>
> My problem is that the list.name filed doesn't contain the complete n
Hello all,
I have a little problem to solve.
I have a "List" table that lists the tables contained in the same DB. This
table has a "Name" filed that contains such names.
My problem is that the list.name filed doesn't contain the complete name of
the tables but it misses a "prestring". Say: a ta
Hi,
RE:
> In other words, you cannot use values that are determined from
> the selected rows to determine which rows to select. :-)
Sounds very logical. In fact I was not surprised that my query did not
work, I just had no idea about the workaround.
Thanks again!
Gaspar
--
MySQL General Maili
kos <[EMAIL PROTECTED]>
To: mysqllist <[EMAIL PROTECTED]>
Subject: How to include count(*) in selection criteria
Dear all,
I am trying the following mysql statement (probably it will be obvious
that I am a newcomer):
select IMdirectory, IMljd, count(*) as N from Images where IMstid
= 5,
At 12:52 -0500 12/17/03, Gaspar Bakos wrote:
Dear all,
I am trying the following mysql statement (probably it will be obvious
that I am a newcomer):
select IMdirectory, IMljd, count(*) as N from Images where IMstid
= 5, N > 10 group by IMdirectory order by IMljd;
ERROR 1054: Unknown column 'N' in
Dear all,
I am trying the following mysql statement (probably it will be obvious
that I am a newcomer):
select IMdirectory, IMljd, count(*) as N from Images where IMstid
= 5, N > 10 group by IMdirectory order by IMljd;
ERROR 1054: Unknown column 'N' in 'where clause'
My question is: how could I
ent: Tuesday, February 04, 2003 9:25 AM
To: [EMAIL PROTECTED]
Subject: Opposite selection...
Ok I'm stumped on what I think should be a somewhat simple query. What I
have so far is a list of names that is in a list of projects AND in a the
main contact list by doing the following query:
SELECT
I think you are coming from the wrong angle. If you want the opposite,
you should reverse your approach. Select all the names in the projects
table, match them with names in the contacts, then filter it so you only
have the ones without a match.
SELECT p.name,p.company FROM projects p
LEFT JOIN
Hi.
On Tue 2003-02-04 at 12:25:08 -0500, [EMAIL PROTECTED] wrote:
> Ok I'm stumped on what I think should be a somewhat simple query. What I
> have so far is a list of names that is in a list of projects AND in a the
> main contact list by doing the following query:
> SELECT p.name, p.company FROM
Nicholas,
Tuesday, February 4, 2003, 12:25:08 PM:
> Ok I'm stumped on what I think should be a somewhat simple query. What I
> have so far is a list of names that is in a list of projects AND in a the
> main contact list by doing the following query:
> SELECT p.name, p.company FROM contacts c, pro
refer to 1.7.4.1 Subselects in the manual for the
answer. here's a brief example:
The queries:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM
table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM
table2 WHERE table1.id=table2.id);
Can be rewritten as:
SELECT table1.* FROM tab
Ok I'm stumped on what I think should be a somewhat simple query. What I
have so far is a list of names that is in a list of projects AND in a the
main contact list by doing the following query:
SELECT p.name, p.company FROM contacts c, projects p WHERE
CONCAT(c.firstName, " ", c.lastName) = p.name
: [EMAIL PROTECTED]
Subject: Continuing a previous selection
I want to select a large amount of data from the database, but I can't
use it all at once. Is there a way to "pick up where I left off" and
get another X rows of data?
For instance, suppose I have the following tabl
I want to select a large amount of data from the database, but I can't
use it all at once. Is there a way to "pick up where I left off" and
get another X rows of data?
For instance, suppose I have the following table in a database :
CREATE TABLE namelist (nameid INTUNSIGNEDNOT NULL
name
Now for the questions -
I would like to be able to basically do the reverse of what I
just did above. That is,
I would like to be able to view the data where 'Included'
commands do not have any 'Included' tests.
I would also like to view the 'Included' commands
JOIN is what you need.
http://www.mysql.com/doc/J/O/JOIN.html
Some examples:
mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1
> Well..complex..for me at least!
>
> I have a table containing story's
> All storys have one unique `storyID`
> I have another table where I keep track of user's favorite story's.
> This table has two columns `clientID`and `storyID`
> after a while, you could end up having the following rows in
Well..complex..for me at least!
I have a table containing story's
All storys have one unique `storyID`
I have another table where I keep track of user's favorite story's.
This table has two columns `clientID`and `storyID`
after a while, you could end up having the following rows in favorite
docum
Hi "MySQL",
> I record some stock data into MySQL, I'm looking for the correct
> syntax for a SELECT statement.
>
> SELECT min(Tmin) FROM quotes WHERE low=min(low)
>
> In short, I want the time of the occurance of the low for the day,
> Tmin is time column. Once the low is achieved, all the rema
I record some stock data into MySQL, I'm looking for the correct
syntax for a SELECT statement.
SELECT min(Tmin) FROM quotes WHERE low=min(low)
In short, I want the time of the occurance of the low for the day,
Tmin is time column. Once the low is achieved, all the remaining data
for the day ha
- Original Message -
From: "muruganandam" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 05, 2001 4:04 PM
Subject: selection from multiple tables;
>
> for a particular query i have to select data from five tables.
> can any one s
for a particular query i have to select data from five tables.
can any one suggest me a best way to do that in terms of performace..
thanks in advance
muruganandam
-
Before posting, please check:
http://www.mysql.com/manua
Hi!
> "Eric" == Eric DeCosta <[EMAIL PROTECTED]> writes:
>> Description:
Eric> Index analysis and optimization is choosing wrong index when a
Eric> table involved in a JOIN is compound. MySQL should still be using
Eric> the first part of the GECK_STATE PRIMARY key (geck_id) since it is t
-+
>Fix:
Temporary work-around is not to use a compound index and/or create additional,
separate indexes. I suppose throwing the "safe" switch on mysqld would also work.
>Submitter-Id: michael6218
>Originator:
>Organization:
The MathWorks, I
On 06-Jul-01 Roger Ramirez wrote:
> I have a database of 5.6 million records and I need to choose 5000 random
> records. What would that query look like?
>
SELECT foo from bar ORDER BY RAND() LIMIT 5000
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's alw
> I have a database of 5.6 million records and I need to choose 5000 random
> records. What would that query look like?
select * from records order by rand() limit 5000;
destiney - (des-ti-ny) - n. 1. deity of all things "
I have a database of 5.6 million records and I need to choose 5000 random
records. What would that query look like?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/
Nicola Sabbi writes:
> HI,
> I have the following table:
>
>
> desc keywords_news;
> ++--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> ++--+--+-+-+---+
> | id_keyword | int(11) |
HI,
I have the following table:
desc keywords_news;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| id_keyword | int(11) | | PRI | 0 | |
| id_new
Thank paul. I know about Date_format, but was curious if there is some
setup parameters would allow us to define the MySql date format .
Paul DuBois wrote:
> On Fri, Jan 26, 2001 at 01:40:12PM -0700, Web master wrote:
>
>> Hello,
>>
>> I would like to change the default date format MySql from
On Fri, Jan 26, 2001 at 01:40:12PM -0700, Web master wrote:
> Hello,
>
> I would like to change the default date format MySql from /mm/dd to
> dd/mm/. Does anyone know, how to make this change in MySql?
You can't.
However, you can select DATE, DATETIME, or TIMESTAMP values using
DATE_F
Hello,
I would like to change the default date format MySql from /mm/dd to
dd/mm/. Does anyone know, how to make this change in MySql?
Thanks.
-
Before posting, please check:
http://www.mysql.com/manual.php (the
87 matches
Mail list logo
