In the last episode (Mar 02), wang shuming said:
Hi,
Any table with a ordno char(n) not null field
for example
ordno qty
35
0
1
'abc' 3
'000' 100
select * from table1 where ordno' ' or ordno=' '
5 rows
At 08:59 PM 2/27/2010, you wrote:
Hello everyone,
How would I select a random row that changes daily?
Thanks
The common way would be to do:
select * from table order by rand() limit 1;
You can of course add a Where clause to select only those rows that were
added today.
select * from
Hello everyone,
How would I select a random row that changes daily?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
...I am using PHP 5.2
Hello everyone,
How would I select a random row that changes daily?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=ja...@jasoncarson.ca
--
MySQL General Mailing List
At 08:59 PM 2/27/2010, you wrote:
Hello everyone,
How would I select a random row that changes daily?
Thanks
The common way would be to do:
select * from table order by rand() limit 1;
You can of course add a Where clause to select only those rows that were
added today.
select * from
Dante,
On Tue, Dec 22, 2009 at 3:53 PM, Dante Lorenso da...@lorenso.com wrote:
All,
There was a feature of another DB that I have grown extremely accustomed to
and would like to find the equivalent in MySQL:
UPDATE mytable SET
mycolumn = mycolumn + 1
WHERE mykey = 'dante'
RETURNING
update statement to become a select statement also where the rows
affected by the update can also be returned. This works for multiple rows
or just one and is how I have been able to do in 1 step what otherwise seems
to require many.
In MySQL, I have found this so far:
UPDATE mytable SET
Ok, I feel silly for asking this, but I am going to do it anyway.
I have a huge stored procedure that does quite a bit of logic, and
gathering/splitting of data. I currently have our customer database on one
server, and our logging on another. What i need to do, is to pull the
customer id from
Is this possible to do? To make a connection, inside the
stored procedure
to a completely different machine and access the mysql there?
The only way I know to access tables from different servers
from a single connection is federated tables:
Posted this before, but beware: federated tables do NOT use indices. Every
select is a full table scan, and if you're talking about a logging table
that could become very expensive very fast.
On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal n...@jammconsulting.comwrote:
Is this possible to do
Hello Johan,
On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:
Posted this before, but beware: federated tables do NOT use indices.
Every
select is a full table scan, and if you're talking about a logging
table
that could become very expensive very fast.
This is not entirely true
, but I guess my searching keywords were
insufficient ;)
Steven Staples
-Original Message-
From: harrison.f...@sun.com [mailto:harrison.f...@sun.com]
Sent: December 9, 2009 2:07 PM
To: Johan De Meersman
Cc: Neil Aggarwal; Steven Staples; mysql@lists.mysql.com
Subject: Re: Select from
Steve:
I suppose maybe making this a slave table
to the other
server... nah... lots of work there
Setting your local server to be a slave of the
remote server is not too hard and would
be a MUCH better solution.
The steps are fairly staightforward:
1. Add a slave user to the remote
On Sun, Dec 6, 2009 at 2:42 PM, Steve Edberg edb...@edberg-online.comwrote:
At 1:26 PM -0500 12/6/09, Victor Subervi wrote:
Hi;
I have the following:
mysql select * from categoriesProducts as c inner join
relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p
Hi;
I posted this Saturday. Perhaps it's too challenging for those who read it
to answer. I hope someone can.
I need to write a select statement that enables me to select column 'ID'
from a table where a certain value is found in an enum of a specific column.
For example...
select column_type
On 12/7/09 5:26 AM, Victor Subervi victorsube...@gmail.com wrote:
Hi;
I posted this Saturday. Perhaps it's too challenging for those who read it
to answer. I hope someone can.
I need to write a select statement that enables me to select column 'ID'
from a table where a certain value
On Mon, Dec 7, 2009 at 8:08 AM, Tom Worster f...@thefsb.org wrote:
On 12/7/09 5:26 AM, Victor Subervi victorsube...@gmail.com wrote:
Hi;
I posted this Saturday. Perhaps it's too challenging for those who read
it
to answer. I hope someone can.
I need to write a select statement
Hi;
I have the following:
mysql select * from categoriesProducts as c inner join relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID
where p.Category = prodCat2;
ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause'
mysql describe
At 1:26 PM -0500 12/6/09, Victor Subervi wrote:
Hi;
I have the following:
mysql select * from categoriesProducts as c inner join relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID
where p.Category = prodCat2;
ERROR 1054 (42S22): Unknown column
Hi;
I need to write a select statement that enables me to select column 'ID'
from a table where a certain value is found in an enum of a specific column.
For example...
select column_type from information_schema.columns where
table_name='products' and column_name='Categories';
will give me
I am trying top run these 2 SELECT queries using mysql_real_query in MySQL-C.
The only difference between them is changing the first hex value from 41 to 01.
It if is 41, the query runs fine. When I change it to 01 I get the following
segmentation fault.
segfault at 0 ip 00402be6 sp
Hi\I think you can achieve this using a single query like this: SELECT * FROM
approvals WHERE adminname != 'admin2'and photo_uid in (select photo_uid from
approvals where adminname='admin2'), of course, maybe it's not the best
solution, just for your information.在2009-11-22，Ashley M. Kirchner
I'm sorry for missing the key word not, it should be SELECT * FROM approvals
WHERE adminname != 'admin2'and photo_uid not in (select photo_uid from
approvals where adminname='admin2'),
在2009-11-22，shjunsin shjun...@163.com 写道：
Hi\I think you can achieve this using a single query like
||
| adminname | varchar(100) | NO | | NULL
||
| status| int(1) | NO | | NULL
||
+---+--+--+-+-++
SELECT * FROM approvals
Hello list
I have a list of names with english and greek characters.
How can select them separately?
I mean, only greeks or only english.
thank you,
Nikos
Hi,
If the efficiency is the key factor I would suggest to create a trigger on
insert and update and mark the rows in a separate column instead of
executing some fancy string checks during select.
Regards,
m.
-Original Message-
From: nikos [mailto:ni...@qbit.gr]
Sent: 19 November 2009
I find a solution that works:
SELECT writer_id, writer FROM writer WHERE writer REGEXP '^[A-Z]+' ORDER
BY writer
Thank you all
Nikos
misiaQ wrote:
Hi,
If the efficiency is the key factor I would suggest to create a trigger on
insert and update and mark the rows in a separate column instead
on billing.debits
for each row
begin
declare total_debits int;
declare total_credits int;
declare total_balance int;
select SUM(debits.amount) into total_debits from debits where
debits.enabled=1 and account=new.account;
select SUM(credits.amount) into total_credits from credits where
It appears to be a simple enough error message. Here is your trigger
you are reffering quite explicitly to credits.enabled:
select SUM(credits.amount) into total_credits from credits where
credits.enabled=1 and account=new.account;
and this table has no such column defined. debits does
:
select SUM(credits.amount) into total_credits from credits where
credits.enabled=1 and account=new.account;
and this table has no such column defined. debits does, but not this one
CREATE TABLE `credits` (
`account` int(11) NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP
I seem to recall a SQL select syntax along these lines:
SELECT col1, col2
WHERE col1 IN (set)
Is this or similar syntax in MySQL or is my dotage coming upon me
Thanks in advance,
Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
There is!
But I would definitely check the online doc for further and more complete
info.
Cheers!
Claudio
On Oct 12, 2009 9:47 PM, Bruce Ferrell bferr...@baywinds.org wrote:
I seem to recall a SQL select syntax along these lines:
SELECT col1, col2
WHERE col1 IN (set)
Is this or similar syntax
that's legal where set is a comma-delimited list of items of the same
datatype as col1
On Mon, Oct 12, 2009 at 2:41 PM, Bruce Ferrell bferr...@baywinds.orgwrote:
I seem to recall a SQL select syntax along these lines:
SELECT col1, col2
WHERE col1 IN (set)
Is this or similar syntax in MySQL
Hi,
I want to search all rows with datum = 'tuesday' for example, something
like:
SELECT * FROM `table_anw` WHERE datum=DAYOFWEEK(3);
'3' as tuesday.
I found in the manual I can do:
SELECT DAYOFWEEK('1998-02-03');
and will get '3' for Tuesday.
But thats not what I want. I want to select only
Have you tried this?
SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
Kerstin Finke kerstinfi...@hotmail.com escreveu na mensagem
news:20090929130406.9802.qm...@lists.mysql.com...
Hi,
I want to search all rows with datum = 'tuesday' for example, something
like:
SELECT * FROM
Try this
SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
Scott Swaim
I.T. Director
Total Care / Joshua Family Medical Care
(817) 297-4455
Website: www.totalcareclinic.com
NOTICE: The information contained in this e-mail is privileged and
confidential and is intended for the exclusive
SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
SELECT * FROM `table_anw` WHERE WEEKDAY(datum) = 2;
SELECT * FROM `table_anw` WHERE DAYNAME(datum) = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%W') = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%a') = 'Tue
Does anyone know if I can add a hint SQL_BUFFER_RESULT to INSERT .. SELECT ON
DUPLICATE
ex..
INSERT INTO foo
SELECT SQL_BUFFER_RESULT* FROM bar
ON DUPLICATE KEY UPDATE foo.X=..
Both my tables foo and bar are InnoDB; but the idea is to release the lock on
bar as soon as possible by moving
.
I have found this to work except I am not sure how to pass a where clause
for the rownum part:
SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t
I was trying something like:
SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t
Is there anyway the SELECT query can be forced to use the from and to
rownum parameters?
1st LIMIT arg = OracleFromArg
2nd LIMIT arg = OracleToArg - OracleFromArg + 1
so 'from 11 to 20' becomes LIMIT 11,10.
PB
-
Anoop kumar V wrote:
Hi All,
I am facing a problem in porting
Never mind. I got it to work..
I had to really trim down the entire statement:
set @sql = concat( select
iams_id as iamsId
,division_name as divisionName
,region_name as regionName
,isactive as isActive
from user_approvers
Hello.
I have 4 tables:
MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)
And a search box.
A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get
select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = anything or
T2.Source2_Name = anything or
T3.Source3_Name = anything
Not tested
Should be more efficient to do something like:
SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name'
UNION
SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name'
UNION
SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name'
-Original Message-
From: João Cândido de Souza Neto [mailto:j
There are many ways to get the same result. hehehehe
Gavin Towey gto...@ffn.com escreveu na mensagem
news:30b3df511cec5c4dae4d0d290504753413956dc...@aaa.pmgi.local...
Should be more efficient to do something like:
SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name'
UNION
SELECT Main_ID FROM
the
problem
maybe caused by one of the col which is text type, each record of this
col
has 2000 characters. this makes the size of record more biger.
2009/7/13 Darryle Steplight dstepli...@gmail.com
You are still doing SELECT * . Do you really need to return all of the
columns
,
PRIMARY KEY (`SEQ_ID`),
KEY `index_ref_start` (`REF_ID`,`START_POSITION`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
i create a index on cols REF_ID and START_POSITION, i also use analyze table
REF_SEQ to optimization the query,
and now the explain output is:
mysql explain select * from
and START_POSITION, i also use analyze
table REF_SEQ to optimization the query,
and now the explain output is:
mysql explain select * from REF_SEQ where START_POSITION between 3
and 803;
++-+-+--+---+--+-+--++-+
| id
sorry for my careless,the sql should be select * from REF_SEQ where REF_ID =
3 and START_POSITION between 3 and 803;
the explain output is :
mysql explain select * from REF_SEQ where REF_ID = 3 and START_POSITION
between 3 and 803
It looks like MySQL is using both columns in the key for that query, since
the key_len is 8, but for some reason it says it is still using where.
What happens when you only select these fields: seq_id, ref_id,
start_position, end_position?
Does the query speed up? I had a table that had some
yes,it is more faster that i select every cols except the TEXT col,but
unfortunately i need the TEXT cols for next step.
2009/7/14 Johnny Withers joh...@pixelated.net
It looks like MySQL is using both columns in the key for that query, since
the key_len is 8, but for some reason it says
Hi all,
i use select * from table_name where start_postion between min_postion and
max_postion to select all the record in the ranges,
when the ranges is very large,such as 800(about 1000 record in it), the
query is so slow,
when i use mysql administrator i find that traffic is higher when
1. Don't use SELECT *. Only grab the cols that you only need. Also
make sure you have an index on min_position and max_position. After
that if your query isn't faster please show us the output of running
EXPLAIN select * from table_name where start_postion between
min_postion and
max_postion
thanks for reply,
i hava an index on the start_position,the min_postion and the max_postion is
constant value, the output of the query is:
explain select * from REF_SEQ where START_POSITION between 3 and
803
You are still doing SELECT * . Do you really need to return all of the
columns in that table or just COL1, COL2, COL5 for example. Only grab
the columns you are actually going to use.
On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote:
thanks for reply,
i hava an index
sorry fo that, but i really need all cols in the table, i think the problem
maybe caused by one of the col which is text type, each record of this col
has 2000 characters. this makes the size of record more biger.
2009/7/13 Darryle Steplight dstepli...@gmail.com
You are still doing SELECT
wrote:
sorry fo that, but i really need all cols in the table, i think the problem
maybe caused by one of the col which is text type, each record of this col
has 2000 characters. this makes the size of record more biger.
2009/7/13 Darryle Steplight dstepli...@gmail.com
You are still doing SELECT
of the col which is text type, each record of this
col
has 2000 characters. this makes the size of record more biger.
2009/7/13 Darryle Steplight dstepli...@gmail.com
You are still doing SELECT * . Do you really need to return all of the
columns in that table or just COL1, COL2, COL5 for example
Hello,
We are experience an estrange problem with mysql and we need help to debug
this properly.
We have a mysql server 5.0.67 on a ubuntu intrepid.
Sometimes we have one unique query that cannot be executed and we get this
error:
SELECT command denied to user 'user'@'localhost' for table
Hello!
I cannot figure out the fastest way to do a select on the floowing field:
f_spectinyint not null;
It is a table of 100 000 records of products and f_spec is set only for
about 200 products.
I figure it could be done in two ways:
1) create an index on f_spec and do simple
select
is much slower than using an index.
2009/6/15 Artem Kuchin mat...@itlegion.ru:
Hello!
I cannot figure out the fastest way to do a select on the floowing field:
f_spec tinyint not null;
It is a table of 100 000 records of products and f_spec is set only for
about 200 products.
I figure
Dear All
I'm trying to insert a bunch of data from TableA in TableB by doing
SELECT INTO TableB (fieldA, fieldB, ...)
SELECT fieldA, fieldB, ... FROM TableA GROUP BY fieldA, fieldC, ...
ON DUPLICATE KEY UPDATE fieldZ = VALUES(fieldZ);
On my PC this works fine. But on the Server, not all rows
Hi
I have created the following 2 views:
CREATE VIEW `cpes_noise_num` AS
SELECT cpes_dsl_line_stats.id_cpes,
sum(IF(cpes_dsl_line_stats.snr_downstream
(SELECT snr_downstream FROM admin_configs WHERE admin_id =0),1,0)) AS
snr_downstream, sum(IF((cpes_dsl_line_stats.bit_errors
In the last episode (Jun 10), Yariv Omer said:
I have created the following 2 views:
CREATE VIEW `cpes_noise_num` AS
[ big view]
CREATE VIEW `my_connect` AS
[ big view joining on cpes_noise_num ]
when I am trying to do something like:
SELECT count(*) from my_connect
It takes 1 minute
Hi,
mysql create table t(i int);
mysql insert into t values(1),(2),(3);
mysql select i, if(i = 1, 'low', 'high') from t order by i;
+--+---+
| i| if(i = 1, 'low', 'high') |
+--+---+
|1 | low |
|2 | high
Can u tell me , assume if i use If in the query , then i reduce performance
,
Any idea
On Mon, May 18, 2009 at 3:19 PM, Janek Bogucki
janek.bogu...@studylink.comwrote:
Hi,
mysql create table t(i int);
mysql insert into t values(1),(2),(3);
mysql select i, if(i = 1, 'low', 'high') from t
Hi all ,
Can u give one example query ,
Which contain the IF condition ,
Because here before am not used the IF and all ,
Thanks
--
உங்கள் நண்பன்
பரணி குமார்
Regards
B.S.Bharanikumar
POST YOUR OPINION
http://bharanikumariyerphp.site88.net/bharanikumar/
Hi Bruce,
SELECT ... INTO OUTFILE always creates the file local to the database server.
If you want to dump results where your perl script is running you'll have to
use another method such as receiving the results of the query normally and
writing the file in the perl script.
Regards
Thanks all who replied.
After I posted I kept looking and found it... Also had folks point it
out to me.
Your suggestion is what I ended up doing.
Bruce
Gavin Towey wrote:
Hi Bruce,
SELECT … INTO OUTFILE always creates the file local to the database
server. If you want to dump
I have a bit of perl code that ends with an error:
$sql=SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status,
a.reason, a.tl
INTO OUTFILE '/application/result.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '\'
LINES TERMINATED BY '\n'
FROM alerts
:
Can someone please help me with this one?
I'm trying to SELECT from a table only those records that have a record,
matching a search term, in a table related by a many to many relationship.
The many to many relationship is in a mapping/junction table.
Here's an example of what I have so far
Can someone please help me with this one?
I'm trying to SELECT from a table only those records that have a record,
matching a search term, in a table related by a many to many
relationship. The many to many relationship is in a mapping/junction table.
Here's an example of what I have so far
From: haidarpes...@gmail.com
To: mysql@lists.mysql.com
Subject: select data from two table and will sort by price in second table
Date: Wed, 29 Apr 2009 10:46:48 +0700
dear all,
please help us mien for select data from two table with details as follows:
primery tabel : bookcatalog
dear all,
please help us mien for select data from two table with details as follows:
primery tabel : bookcatalog
second table : pricelist
for seaching we will try to sort by price (in second table).
our databese details like this:
SELECT id, title, author from bookcatalog where isbn LIKE
I tend to use temporary tables a lot, because I'm doing one-off
manipulations where efficiency is not the primary concern and because it
helps me think things through. Nonetheless, I've been wondering about this:
SELECT `x` FROM `t1` JOIN (SELECT `x` FROM `t2`) AS `t3` on `t1`.`y` =
`t3`.`y
I am trying to select all books whose authors last names begin with I, J
or K. I have 3 tables: book, author and book_author. The following query
works with one condition but not with three.
SELECT * FROM book
WHERE id IN (SELECT bookID
FROM book_author WHERE authID IN (SELECT
On Wed, Apr 1, 2009 at 1:27 PM, PJ af.gour...@videotron.ca wrote:
I am trying to select all books whose authors last names begin with I, J
or K. I have 3 tables: book, author and book_author. The following query
works with one condition but not with three.
SELECT * FROM book
WHERE id
David Giragosian wrote:
On Wed, Apr 1, 2009 at 1:27 PM, PJ af.gour...@videotron.ca
mailto:af.gour...@videotron.ca wrote:
I am trying to select all books whose authors last names begin
with I, J
or K. I have 3 tables: book, author and book_author. The following
query
problem disappeared and the
selects behave as one would expect.
Many thanks to all who offered advice.
Carl
- Original Message -
From: Perrin Harkins per...@elem.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Friday, March 13, 2009 1:40 PM
Subject: Re: Select query
AFAIK, repeated LIKEs.
On Tue, Mar 24, 2009 at 6:24 AM, Yariv Omer yar...@jungo.com wrote:
Hi
when I am using a query for several field's values I am using the following
query:
Select field from table where in ('11', '22')
I need to do a LIKE search (not exact match but like match)
How
Hi
when I am using a query for several field's values I am using the
following query:
Select field from table where in ('11', '22')
I need to do a LIKE search (not exact match but like match)
How can I do it
Thanks, Yariv
--
MySQL General Mailing List
For list archives: http
3 tables are related by one-many links.
Employees Assets Maintenance
Employees can be assigned = 0 Assets
Assets can have = 0 occurances of Maintenance.
Assets table contains EmployeeIDs and MaintenanceIDs,
but no Foreign Key contraints.
Queries ...
1) which Employees
On Tue, Mar 17, 2009 at 12:42 PM, BobSharp bobsh...@ntlworld.com wrote:
These have been written successfully with Sub-Queries,
I would like to know how they can be done with only JOINs ?
http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html
- Perrin
--
MySQL General Mailing List
SELECT Employees.*
FROM Employees LEFT JOIN Assets ON Employess.EmployeeID =
Assets.EmployeeID
WHERE Assets.EmployeeID IS NULL
The one for assets with no maintenance is similar. The point is the left
join above produces in its output all rows from the Employees table
regardless of whether
Thanks for that,worked through and found that this gives the correct
result ...
--- Employee No Assets ---
SELECT DISTINCT e.employeeID AS eID,
concat(e.firstname, , e.lastname) AS eName
FROM employees e LEFT JOIN assets a ON e.employeeID = a.employeeID
WHERE
This links to an ERD diagram that illustrates a MySQL database
schema.
www.ProBowlUK.co.uk\images\ERD_001.jpg
They are MyISAM tables, with no Foreign Key contraints
(the fk suffix is not used in the actual database).
I need to provide MySQLSELECT statements, with various
combinations
concurrency doesn't necessarily mean the older versions
that are being read from have to be entirely in memory.
InnoDB will lock on a query that doesn't use an index.
It shouldn't lock on a SELECT query, regardless of the indexes involved.
- Perrin
--
MySQL General Mailing List
For list
suggestions also.
Carl
- Original Message -
From: Brent Baisley brentt...@gmail.com
To: Carl c...@etrak-plus.com
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb
Ok, so you have 687 unique organization serial numbers. That's not
very unique
...@etrak-plus.com
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb
Ok, so you have 687 unique organization serial numbers. That's not
very unique, on average it will only narrow down the table to 1/687 of
it's full size. This is probably the source of your
I have a view which is a 3 table join on a compound index.
I have two indexes: Index1: Product_Code, Store_Id, Date_Sold and
Index2:
Date_Sold,Store_Id,Product_Code
If I execute a select like:
select * from MyView where product_code=123;
it returns
I really appreciate the time you have taken to help me with this problem.
I will be out of the office until around 1:00PM and will try your
suggestions.
I did attach a copy of the query but it may have been stripped somewhere
along the line so I have placed it in line below.
select *
from
ba...@xaprb.com
To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
wrote:
A SELECT will/can lock
Message -
From: Baron Schwartz ba...@xaprb.com
To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
.
Explain (copy as text and copy as Excel attached) seems to indicate that it
is fairly good although the first step does get quite a few rows.
EXPLAIN isn't really relevant to table locking. InnoDB tables should
never let readers block writers for a simple SELECT.
Does anyone have any ideas
One more note.
Perrin asked if I was using any select... for update. The answer is no,
neither in the select query that seems to be locking the tables nor in the
queries that are processing transactions.
Surprisingly, one of the tables that reports being locked is never accessed
the isolation level but I believe it is whatever was set out of
the box (five years ago.)
Thanks,
Carl
- Original Message -
From: Perrin Harkins per...@elem.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 1:49 PM
Subject: Re: Select query
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of
...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status
- Original Message - From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different
301 - 400 of 4787 matches
Mail list logo
