Greetings, all...
I'm having an issue with a SELECT in our system. We have an event log table,
with about 9 million rows in it. Inserts happen with some pretty high frequency,
and these selects happen periodically. The event_log table is MyISAM, the
rest of the tables are InnoDB.
What's
I had this same issue a while back and solved it by writing my events to
a disk-based file and periodically importing them into the event log
MyISAM table. This way, even if your select statements lock the table,
it won't affect the performance of your application. Of course, this
may
Do you delete data from the table ?
MyISAM will only grant a write lock when there are no locks on the table -
including implicit read locks. That may be your problem.
There is a single situation when concurrent reads and writes are possible on
MyISAM, however: when your table has no holes in
What columns do you have indexed on your event_log table? Can you post the
output from SHOW CREATE TABLE? How long does the query run for?
-Original Message-
From: Andy Wallace [mailto:awall...@ihouseweb.com]
Sent: Friday, February 04, 2011 10:29 AM
To: mysql list
Subject: Table/select
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 on
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
I am having a problem with SELECT. The table has 3 rows. I am using
the C api. Here is my C code.
count = mysql_query(my_db, SELECT * FROM accounts);
er = mysql_error(my_db);
res = mysql_use_result(my_db);
num_row = mysql_num_rows(res);
count is returned as 0 (no error)
er is
Dear all,
I've encountered a very strange problem. I dumpped one table from a
database and then import it into another database. I works successfully,
all the data is exactly the same. But when I the below SQL on two tables:
select * from foo_table ORDER BY id DESC
it shows up different result.
I get this error:
Fehler in
/home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php
in Zeile 36 mit Error:br
Query:INSERT INTO objektflyer_verknuepfung
(av_o_id_haupt,av_o_id_link,av_text,av_op_id) SELECT
418,av_o_id_link,av_text,av_op_id FROM objektflyer_verknuepfung WHERE
Hi Barry,
see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
you cannot insert into a table you're doing select on
(same goes for update).
Regards,
/Johan
Barry skrev:
I get this error:
Fehler in
/home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php
Johan Höök schrieb:
Hi Barry,
see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
you cannot insert into a table you're doing select on
(same goes for update).
But i am doing it on a test server version 5.x and it works like a charm :)
--
Smileys rule (cX.x)C --o(^_^o)
Dance for
Hi,
The following query worked fine:
INSERT INTO Allocations(Project_ID, User_ID)
SELECT P.Project_ID, U.User_ID
FROM Users U, Projects P, Clients C
WHERE P.Client_ID = C.Client_ID
AND U.Client_ID = C.Client_ID
AND Project_ID =.$project_id)
However I want to add a column to the INSERT part of
)
Met vriendelijke groet,
Almar van Pel
-Oorspronkelijk bericht-
Van: Shaun [mailto:[EMAIL PROTECTED]
Verzonden: woensdag 23 november 2005 15:22
Aan: mysql@lists.mysql.com
Onderwerp: INSERT SELECT Problem
Hi,
The following query worked fine:
INSERT INTO Allocations(Project_ID, User_ID
YES_COLUMN with the column name that stores the 'Yes' values.
Best regards,
Diego Wald
- Original Message -
From: Shaun [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, November 23, 2005 11:21 AM
Subject: INSERT SELECT Problem
Hi,
The following query worked fine:
INSERT
Hello,
I'm trying to get the date and amount of the most visits to my site over a
given time period using :
SELECT max(visits) as maximum FROM visit WHERE (((visit_date =
'$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id))
This gives me the right figure, but when I try to
Lee Denny wrote:
Hello,
I'm trying to get the date and amount of the most visits to my site over a
given time period using :
SELECT max(visits) as maximum FROM visit WHERE (((visit_date =
'$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id))
This gives me the right figure,
Lee Denny wrote:
Hello,
I'm trying to get the date and amount of the most visits to my site over a
given time period using :
SELECT max(visits) as maximum FROM visit WHERE (((visit_date =
'$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id))
This gives me the right figure,
Reni Fournier wrote:
Thanks for the solution. It looks like it would work, but I don't have
MySQL 4.1 (which I believe is required for this to work, since this is
SUBSELECT, isn't it?).
Assuming I have to use two selects, which would you say is faster,
creating a temporary table in MySQL,
I'm having a really hard time selecting rows from a table in one SELECT
statement. I can do it in two SELECTS, but it seems I should be able to
do it in one.
TRIPS
id dateperson_id cost
René Fournier [EMAIL PROTECTED] wrote on 06/02/2005 02:53:51 PM:
I'm having a really hard time selecting rows from a table in one SELECT
statement. I can do it in two SELECTS, but it seems I should be able to
do it in one.
TRIPS
id date person_id cost
Hi René,
thsi can be a solution, many others are possible :
mysql select distinct the_date, person_id, cost, name
- from trips,persons
- where person_id=persons.id
- and the_date in(select max(the_date) from trips a
- where a.person_id=person_id
- group by person_id)
-
Thanks for the solution. It looks like it would work, but I don't have
MySQL 4.1 (which I believe is required for this to work, since this is
SUBSELECT, isn't it?).
Assuming I have to use two selects, which would you say is faster,
creating a temporary table in MySQL, or extracting the data
Hi,
I am using MySQL 4.0.20. For a table of INNODB type, same query return different
results when different query plan is used.
select * from project_team where project_id = 'FMS ';
-- 2 rows. (primary key used)
select * from project_team ignore index (PRIMARY) where project_id = 'FMS ';
Has anyone seen a post on this issue? If not, can anyone offer any advice?
I have a TBL of users and I have created a search screen where you can type
in first or last name and it will retrieve the appropriate records. Here is
the statement:
Select * from STUDENTS WHERE FName LIKE '%
Are you spitting out an output of the query string to verify that the data
from the form is making it to the query correctly?
GOD BLESS AMERICA!
To God Be The Glory!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Hello ,
I'm working on a project with MySql 4.0.12-log.
I have a problem with insert .. select:
To describe the program of some touristic tours I create two tables:
TOUR that contains the data
TAPPE that contains the program of the tour.
(relation 1:n).
To keep track of each tour i create two
I am new to mysql and am converting an existing program. I have
encountered what appears to be a problem with bigints
I have a large integer number (milliseconds since 1970) which is 13
digits. So I tried to store it in a table as a bigint type. Storing
works fine. When I try to retrieve
Hi,
I have a problem about writing a proper SELECT query
for the following goal: (I only have basic knowledge
of SQL)
Table name: peoplelist
column 1: id (not NULL, auto_incremental)
column 2: name
column 3: country
now, there are about 7,000 rows in this table. I want
to select out:
first
Try something like this:
SELECT ID, Name, Country
FROM peoplelist
GROUP BY Country
HAVING count(Country)10;
That might work. Also you can have subselects in 4.0.
sub selects are only in 4.1 I thought?
On Mar 3, 2004, at 5:08 PM, Jacque Scott wrote:
Also you can have subselects in 4.0.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
You are correct. Sorry about that.
charles kline [EMAIL PROTECTED] 3/3/2004 2:46:51 PM
sub selects are only in 4.1 I thought?
On Mar 3, 2004, at 5:08 PM, Jacque Scott wrote:
Also you can have subselects in 4.0.
: SELECT problem
Hi,
I have three tables, a title's table, a genre's table and a genre_titles
map
table (to model the many to many relationship between genre's and
title's).
I need to write a query that will return title's that match two or more
genre's. An example would be one title could
Hi all,
I have a table with the following data:
++---+--+
| lpcval | smiles_id | crhash |
++---+--+
| 0.81 | 996 | 0597b6f84e0feaf9596869284e6e0660 |
| 0.86 |
Dermot Frost wrote:
Hi all,
I have a table with the following data:
++---+--+
| lpcval | smiles_id | crhash |
++---+--+
| 0.81 | 996 |
temporary
table X.
Hope this helps.
Best regards,
Lin
-Original Message-
From: Rachel Cunliffe [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 27, 2003 9:00 PM
To: [EMAIL PROTECTED]
Subject: SELECT problem
Hi,
I'm new at complex SELECT statements
Hi,
I'm new at complex SELECT statements, so any help would be appreciated. I
need to create a summary table of counts from two tables in the database:
year9 has a stack of variables including sex and favsub (favourite subject)
year10 also has a stack of variables including sex and favsub
I'd
Hello everyone,
I have the following select statement
SELECT DISTINCT sessionID, userID, date, time
FROM sti_tracking
WHERE userID = 999
What I want is to have only records with the userID of 99 and where the
sessionID is distinct (meaning only on of each session id). Neither
sessionID nor
02, 2003 10:56 AM
To: [EMAIL PROTECTED]
Subject: Newbie SELECT problem
Hello everyone,
I have the following select statement
SELECT DISTINCT sessionID, userID, date, time
FROM sti_tracking
WHERE userID = 999
What I want is to have only records with the userID of 99 and
where
Tim Winters wrote:
Hello everyone,
I have the following select statement
SELECT DISTINCT sessionID, userID, date, time
FROM sti_tracking
WHERE userID = 999
What I want is to have only records with the userID of 99 and where the
sessionID is distinct (meaning only on of each session id).
Hello everyone,
I have the following select statement
SELECT DISTINCT sessionID, userID, date, time
FROM sti_tracking
WHERE userID = 999
What I want is to have only records with the userID of 99 and where the
sessionID is distinct (meaning only on of each session id). Neither
www.samplingtechnologies.com
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Office: 902 450 5500
Cell: 902 430 8498
Fax:: 902 484 7115
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: July 2, 2003 2:13 PM
To: [EMAIL PROTECTED]
Subject: Re: Newbie SELECT problem
Hello everyone,
I have
Tim Winters wrote:
So what I want to be able to do is single out a user (999) and retrieve
all the sessions he was involved in. But I don't want duplicate session
numbers (one is enough).
Make any more sense?
Yes. The solution I posted earlier should work.
Bruce Feist
--
MySQL General
select sessionID,userID,date,time from sti_tracking
where userID=99 group by sessionID;
hope that works ,though i didn't clearly got
your question,may be some expert suggest better
regards
harsh
On Wed, 2 Jul 2003, Tim Winters wrote:
Hello everyone,
I have the following select statement
Table name sti_tracking
hitID (primary key) (autonumber)
userID
sessionID
date
time
pageName
this might work
select userID,sessionID from sti_tracking where userID=999 group by
sessionID;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
www.samplingtechnologies.com
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Office: 902 450 5500
Cell: 902 430 8498
Fax:: 902 484 7115
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: July 2, 2003 2:13 PM
To: [EMAIL PROTECTED]
Subject: Re: Newbie SELECT problem
Hello everyone,
I have
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999, then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one and
would
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999,
then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one
and
Message-
From: Peter K Aganyo [mailto:[EMAIL PROTECTED]
Sent: July 2, 2003 8:00 PM
To: [EMAIL PROTECTED]
Subject: Re: Newbie SELECT problem
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999, then
the solution given by Mike Hillyer is excellent and should work.
However, when I
Stefan Schulte [EMAIL PROTECTED] wrote:
i am analyzing a very strange behaviour of mysql-3.23-53-log
on a Suse 8.1 system:
I have created a table Customer with a column:
customer_id int(11)
Now i want to select all rows with customer_id=41:
SELECT * from Customer WHERE
Hi all,
i am analyzing a very strange behaviour of mysql-3.23-53-log
on a Suse 8.1 system:
I have created a table Customer with a column:
customer_id int(11)
Now i want to select all rows with customer_id=41:
SELECT * from Customer WHERE customer_id=41;
The result is: Empty set (0.13
Hi
Just try this
select distinct(a.id) from test a , test b where a.code = 23 and b.code = 45
and a.id = b.id
Regards,
-Arul
- Original Message -
From: Robert Gehrig [EMAIL PROTECTED]
To: MySQL List [EMAIL PROTECTED]
Sent: Wednesday, December 04, 2002 10:52 PM
Subject: Select Problem
Hi all
I have a detail table that has multiple records associated with an ID number
Both fields are integers
E.G.
Id Code
4 23
4 27
34 23
34 45
34 28
What I need to find is the Id where the code is 23 and 45 for
Found solution, the right syntax is:
SELECT hl7incom.id
FROM hl7incom, pid_segment
LEFT JOIN pid_segment
ON hl7incom.id = pid_segment.id
WHERE hl7incom.msg LIKE '%PID%'
AND pid_segment.id IS NULL;
Cheers for try of help,
Karsten
Same result, also if I do not define unique index.
Just a
I have two tables
CREATE TABLE pid_segment (
id INT NOT NULL UNIQUE PRIMARY KEY,
msg TEXT)
TYPE=INNODB
CREATE TABLE hl7incom(
id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment
(id).
msg TEXT,
time TIMESTAMP NOT NULL)
TYPE=INNODB
There are few data stored in both tables.
On Mon, 2002-09-30 at 23:44, Gebhardt, Karsten wrote:
I have two tables
CREATE TABLE pid_segment (
id INT NOT NULL UNIQUE PRIMARY KEY,
msg TEXT)
TYPE=INNODB
CREATE TABLE hl7incom(
id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment
(id).
msg TEXT,
time TIMESTAMP
No way, I've already tried this.
I have two tables
CREATE TABLE pid_segment (
id INT NOT NULL UNIQUE PRIMARY KEY,
msg TEXT)
TYPE=INNODB
CREATE TABLE hl7incom(
id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment
(id).
msg TEXT,
time TIMESTAMP NOT NULL)
.
-Original Message-
From: Gebhardt, Karsten [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 12:07 AM
To: '[EMAIL PROTECTED]'
Subject: RE: select problem with not equal syntax
No way, I've already tried this.
I have two tables
CREATE TABLE pid_segment (
id INT NOT NULL UNIQUE
Same result, also if I do not define unique index.
Just a suggestion:
SELECT hl7incom.id
FROM hl7incom, pid_segment
WHERE hl7incom.msg LIKE '%PID%'
AND not(pid_segment.id = hl7incom.id)
GROUP BY hl7incom.id;
Otherwise my only other suggestion would involve using the 'NOT IN'
logic, but I
Here is my table on which I am doing sql query:
++--+-+---+--+-+
| fdr_id | fdr_uid | fdr_form_id | fdr_ff_id | fdr_value
| fdr_date|
12:42 PM
To: [EMAIL PROTECTED]
Subject: select problem
Here is my table on which I am doing sql query:
++--+-+---+-
-+-+
| fdr_id | fdr_uid | fdr_form_id | fdr_ff_id | fdr_value
| fdr_date
I want to select from three tables where there may or may not be a record in
the third table. Table 1 and 2 have a one to one relationship and table 1
and 2 both have a one to many relationship with table three. All three
tables have a column called 'code' and I want to select where code is in
Hello!
This one should work:
SELECT
...
FROM
table1 t1
LEFT JOIN table2 t2 USING (code)
LEFT OUTER JOIN table3 t3 USING(code)
WHERE
t1.code = t3.code
OR t3.code IS NULL
;
Mark Colvin wrote:
I want to select from three tables where there may or may not be a record in
Hello!
This one should work:
SELECT
...
FROM
table1 t1
LEFT JOIN table2 t2 USING (code)
LEFT OUTER JOIN table3 t3 USING(code)
WHERE
t1.code = t3.code
OR t3.code IS NULL
;
Mark Colvin wrote:
I want to select from three tables where there may or may not be a record
Hi,
Use LEFT JOIN instead =
Bye and Good Luck!
--- Mark Colvin [EMAIL PROTECTED] wrote:
I want to select from three tables where there may
or may not be a record in
the third table. Table 1 and 2 have a one to one
relationship and table 1
and 2 both have a one to many relationship with
My SQL query problem:
I met a strange problem:
For example, I have the following table:
Mytable:
No Name Date Project
1 Bob 2002-05-27Bob's project
2 John -00-00 John's project
When I use select * from
Hehe, this is almost identical to the post I submitted yesterday, entitled SELECT
'foobar' = 0
There's a patch in that post, doesn't work for dates but should give an idea of what
has to be done.
The reason is that 'WWW' has to be converted to a date. And the conversion gives it a
value of
How about
SELECT @last:=MAX(Date + 0) from stat;
Eyal Rif wrote:
Hi,
I have a table with the format of :
char(20),int(7),char(32)
the Date char(20) contents in actually a number(seconds since 1970) -
I want to use a select query that will give the max value according to
number
Hi,
I have a table with the format of :
char(20),int(7),char(32)
the Date char(20) contents in actually a number(seconds since 1970) -
I want to use a select query that will give the max value according to
number value of that column.
select @last:=MAX(Date) from stat1;
What I need to know
Hi there,
This is my problem now... I have a table containing different paths, like this:
+--++---++
| Path | X | Y | WaypointNr |
+--++---++
| P1 | 1 | 5 | 1 |
| P1 | 2 | 6 | 2 |
| P1 | 3 | 7 | 3 |
| P1 | 8 | 3 |
development.
Also, there may be better solutions. If so, the list will probably point
them out to you. ;-)
Cheers,
Christian Sage
-Ursprüngliche Nachricht-
Von: Ville Mattila [mailto:[EMAIL PROTECTED]]
Gesendet: Samstag, 8. September 2001 18:17
An: MySQL-mailinglist
Betreff: SELECT-problem
Hi
I've been creating a site for someone using MySQL and PHP4.
Basically the table concerned is structured like this;
id int(5) UNSIGNED Noauto_increment Primary
ship varchar(50) NoIndex
year varchar(15) NoIndex
voyage varchar(50) Yes
sex varchar(50)Yes
I've been creating a site for someone using MySQL and PHP4.
Basically the table concerned is structured like this;
id int(5) UNSIGNED Noauto_increment Primary
ship varchar(50) NoIndex
year varchar(15) NoIndex
Sir, change the type of this column to Date, which
Hi,
I'm having the following sql problem:
I have two datetime fields (start and end), I need to find out if the
month is either equal to start-month or equal to end-month but also
if it's a month between start and end. I also check if the end-date
is greater than today so it's a current one.
My
Hi,
I'm having the following sql problem:
I have two datetime fields (start and end), I need to find out if the
month is either equal to start-month or equal to end-month but also
if it's a month between start and end. I also check if the end-date
is greater than today so it's a current one.
My
Hi All
I am trying to compare information in 2 different tables and return
matches. The problem is that this query only returns the listings where
I want it to return a row from AutoEmail when it finds a match.
What am I doing wrong?
SELECT * FROM AutoEmail, listings WHERE
Hi,
I having trouble working out how to get a result set similar to the
following where I select from a table with Date Sales column.
My specific question is can I have a column that accumulates values,
if so could I have some guidance on how to express this in a select
statement please.
Hi,
I having trouble working out how to get a result set similar to the
following where I select from a table with Date Sales column.
My specific question is can I have a column that accumulates values, if so
could I have some guidance on how to express this in a select statement please.
On Fri, 2 Mar 2001, Richard Vibert wrote:
Hi,
I having trouble working out how to get a result set similar to the
following where I select from a table with Date Sales column.
My specific question is can I have a column that accumulates values, if so
could I have some guidance on how
Hi,
At 01:52 pm 2/03/2001, Thalis A. Kalfigopoulos wrote:
On Fri, 2 Mar 2001, Richard Vibert wrote:
Hi,
I having trouble working out how to get a result set similar to the
following where I select from a table with Date Sales column.
My specific question is can I have a column that
Now that I notice more closely the numbers, my answer was obviously wrong with regard
to the 3rd column :o)
Very interesting question...but I doubt there is a SQL way to do that.
Looking fwd to what the rest will sugest.
cheers,
thalis
On Fri, 2 Mar 2001, Richard Vibert wrote:
Hi,
At
Can't you do something with SUM() to get the results, possibly in
coordination with GROUP BY?
--Nathan
On 2001.03.01 23:49:28 -0500 Thalis A. Kalfigopoulos wrote:
Now that I notice more closely the numbers, my answer was obviously wrong
with regard to the 3rd column :o)
Very interesting
On Fri, 2 Mar 2001, Nathan Clemons wrote:
Can't you do something with SUM() to get the results, possibly in
coordination with GROUP BY?
--Nathan
Not to my knowledge/imagination. What are you going to group by? You want and
incremental grouping or better you want a dynamic calculation
Linux , Apache, MySQL
I have a problem in my MySQL database, this is the senario:
I made an update statement on my table by wrong, then I restor my
backup
copy by copying the backup files on the exist ones without stopping the
MySQL.
Now I have problems in searching data:
for example when I made
Try rebuilding the indexes.
-Original Message-
From: Alaiddin Tayeh [SMTP:[EMAIL PROTECTED]]
Sent: 15 February 2001 10:34
To: [EMAIL PROTECTED]
Subject: Select Problem
Linux , Apache, MySQL
I have a problem in my MySQL database, this is the senario:
I made an update
Sir, Joe Celko's 'SQL For Smarties' has two chapters devoted to tree
problems. After a quick look in the book, it appears to me that you
can use one of his algorithms if you restructure your table and adapt
his SQL to the MySQL dialect. See the chapter on Nested Set Models.
Bob Hall
Hi,
I
so you want to list all of them starting with the highest level and going
up to the lowest level?
On Mon, 29 Jan 2001, Oliver Joa wrote:
Hi,
I have a recursive Problem. I have a Table with columns "id", "name" and
"pid". E.g:
name1
name11
name12
name121
name122
name13
I am performing a query in two tables and output it into a temporary table
for further sorting.
The problem is that in one of the tables (TableB), I have an additional id
that I want to be added to the temptable. This id is not present in TableA.
I get this error message when I run the query for
88 matches
Mail list logo
