Maybe I'm wrong :)
On Tuesday, July 21, 2009, John Daisley john.dais...@mypostoffice.co.uk 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.
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,
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
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
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. The
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 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, then
run
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 from images where (condition) order
-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 [EMAIL PROTECTED]
wrote:
$sql
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 flat
-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
[EMAIL PROTECTED] wrote:
I might
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
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 = x.id)
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
226 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) and sum(u3). The other value should be replaced
by zero.
e.g. for the first line (summ(uu) changed to 0)
mnr kw
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'form';
$query = SELECT column1 FROM table
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, column4 base on the selection of the list box.
echo'form';
$query
on the selection of the list box.
echo'form';
$query = SELECT column1 FROM table;
$result = mysql_query($query);
echobr;
echobr;
echocenter;
echoselect NAME='R';
echooption value='NULL'Choose a Category:/option
;
while ($line = mysql_fetch_array($result
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
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 filesort
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
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
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,
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 have
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 /*/
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/
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
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)
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/
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 clause?
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: mysql@lists.mysql.com
Sent: Wednesday, July 20, 2005 3:04 PM
Subject: random rows selection
Hello,
How to select 5 random
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 return
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
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
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 could
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/
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=2
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
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
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 it. Thanks
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 species_a, 1 species_b and 1 species_c
With the select
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
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%'
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
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
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,
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,
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|
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' and
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
:
Fax to:
06/28/2004 02:43 Subject: Re: Duplicate selection with
change
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 table
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 name of
the
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:
SELECT *
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 supposed it...
What
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
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
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, N 10 group
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
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
, 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 p.name, p.company FROM
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
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,
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
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
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
To: [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 table in a database
the 'Included' commands that do not
have either 'Included' or 'Excluded' tests.
I have tried a number of combinations for SELECTion with JOINS
and such, but have not been able to come up with something
that is correct. (As mentioned at the first, I actually have
all this information in a very large
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
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
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
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
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 remaining
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:
- 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 suggest me a best way to do that in terms
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 the column
, MA 01760
MySQL support: extended email support
Synopsis: Incorrect index selection when potential index is compound
Severity: serious
Priority: medium
Category: mysql
Class: doc-bug
Release: mysql-3.23.40 (Source distribution)
Environment:
System: SunOS
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/
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
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
HI,
I have the following table:
desc keywords_news;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| id_keyword | int(11) | | PRI | 0 | |
|
Nicola Sabbi writes:
HI,
I have the following table:
desc keywords_news;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| id_keyword | int(11) | |
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
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
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
87 matches
Mail list logo
