Re: Help with query using IN()
On 04/10/2005, Jasper Bryant-Greene wrote: Kishore Jalleda wrote: Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,189 0,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106 ,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); There is an index on templateid also this query takes ~ 0.04xx seconds to run I would like it to be in 0.00xx range explain select gives this id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE template range PRIMARY PRIMARY 4 NULL 40 Using where It's using a primary key and only examining the 40 rows which you asked for, so that's about as optimised as you'll get for that query. You could always make the actual server faster... If your template table contains many columns in addition to templateid, title and template, and title and template are not TEXT columns, you can consider a covering index on templateid, title and template: ALTER TABLE template ADD UNIQUE (templateid, title, template); -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query using IN()
Hi All, Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); There is an index on templateid also this query takes ~ 0.04xx seconds to run I would like it to be in 0.00xx range explain select gives this id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE template range PRIMARY PRIMARY 4 *NULL* 40 Using where Thanks for the help Kishore Jalleda
Re: Help with query using IN()
Kishore Jalleda wrote: Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); There is an index on templateid also this query takes ~ 0.04xx seconds to run I would like it to be in 0.00xx range explain select gives this id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE template range PRIMARY PRIMARY 4 *NULL* 40 Using where It's using a primary key and only examining the 40 rows which you asked for, so that's about as optimised as you'll get for that query. You could always make the actual server faster... -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HELP PLEASE - C API code help: UPDATE query using result from SELECT
Hi,
When I try and use the REPLACE function such as:
REPLACE INTO table1 SELECT table2.ID, table2.Modified FROM table1 INNER JOIN table2 ON
table1.Company=table2.Company;
I get:
ERROR 1066: Not unique table/alias: 'table1'
Here is the description of both tables:
mysql describe table1;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ID | int(11) | YES | | NULL| |
| Company | varchar(20) | | PRI | | |
| Modified | int(11) | YES | | NULL| |
+--+-+--+-+-+---+
mysql describe table2;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ID | int(11) | YES | | NULL| |
| Company | varchar(20) | | PRI | | |
| Modified | int(11) | YES | | NULL| |
+--+-+--+-+-+---+
Do you know what this error may mean?
Thanks,
John :^)
In response to: -
I am struggling with this myself right now. I posted a similar question to
this group earlier in the day and it must be a poser because no one has
responded.
I fear that the only way to do this with a single sql statement is using the
replace command:
Let's say you have 2 tables like this:
MainTbl
id int not null (primary key),
fld1int,
fld2int,
fld3int
UpdTbl
id int not null (primary key),
fld2int
And let's say that you have 20,000 recs in MainTbl and only 100 recs in
UpdTbl.
You want to join the 2 tables and flag the value from UpdTbl.fld2 into
MainTbl.
fld2, right?
The best approach I have thought of is
REPLACE INTO MainTbl
SELECT MainTbl.id, MainTbl.fld1, UpdTbl.fld2, MainTbl.fld3
FROMMainTbl INNER JOIN UpdTbl ON MainTbl.id = UpdTbl.id;
I have not tried this yet but if I read the manual correctly, it should
work.
If anyone has a better solution, please fill us in.
Additionally, I would like ideas on how to work around MySQL's inability to
handle deletes like the following:
DELETE MainTbl
FROMMainTbl INNER JOIN DelTbl ON MainTbl.id = DelTbl.id;
-Original Message-
From: John Mravnuac [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 19, 2001 8:28 PM
To: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Subject: HELP PLEASE - C API code help: UPDATE query using result from
SELECT
Hi,
I believe that the query below is correct, but I do not believe
that it is
possible in MySQL currently due to it not supporting sub-selects:
UPDATE table1 SET ID=table2.ID, Company=table2.Company,
Modified=table2.Modified FROM table1 INNER JOIN table2 ON
table1.Company=table2.Company WHERE table1.Modified='9';
The error produced was:
ERROR 1064: You have an error in your SQL syntax near 'FROM table1 INNER
JOIN table2 ON table1.Company=table2.Company WHERE table1.Modi' at line 1
If you can't tell from the SQL query above...what I am trying to do is
update data in table1 with data from table2...the two tables have pretty
much identical information, although table2 has updated data and table1
can't just be overwritten because it may have something newer than table2
again (this is only part of th
e entire process which involves two MySQL
servers and a MS SQL server :)
I've read that the only way to do it at the moment is using code
such as C
or C++. Does anyone have any experience with this type of procedure?
My code so far is below...my coding knowledge is very weak and this was
obtained from a text:
#include stdlib.h
#include stdio.h
#include /usr/local/mysql/include/mysql/mysql.h
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
void display_row();
int main(int argc, char *argv[]) {
int res;
uint i = 0;
query = 0;
mysql_init(my_connection);
if (mysql_real_connect(my_connection, localhost, username,
password, database, 0, NULL, 0)) {
gt; printf(Connection success\n);
res = mysql_query(my_connection, SELECT ID, Modified
FROM table1 WHERE Modified = 9);
if (res) {
printf(SELECT error: %s\n,
mysql_error(my_connection));
} else {
res_ptr = mysql_store_result(my_connection);
if (res_ptr) {
while ((sqlrow =
mysql_fetch_row(res_ptr))) {
for (i=0; i
mysql_num_fields(res_ptr); i++)
printf(%s\n,sqlrow[i]);
printf(Fetched data...\n);
/*mysql_query(my_connection,
Some UPDATE code
Re: HELP PLEASE - C API code help: UPDATE query using result from SELECT
Hi. As is described somewhere (http://www.mysql.com/doc/R/E/REPLACE.html), REPLACE mainly behaves like INSERT and therefore the target table may not appear in the SELECT clause (as described here: http://www.mysql.com/doc/I/N/INSERT_SELECT.html). Sorry, but it seems you have to use a temporary table to store the intermediate result. Bye, Benjamin. On Tue, Oct 02, 2001 at 12:39:18PM +1000, [EMAIL PROTECTED] wrote: Hi, When I try and use the REPLACE function such as: REPLACE INTO table1 SELECT table2.ID, table2.Modified FROM table1 INNER JOIN table2 ON table1.Company=table2.Company; I get: ERROR 1066: Not unique table/alias: 'table1' [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP PLEASE - C API code help: UPDATE query using result from SELECT
Hi. On Tue, Oct 02, 2001 at 03:53:12PM +1000, [EMAIL PROTECTED] wrote: Aha, much appreciated...your words therefore the target table may not appear in the SELECT clause have made it clear to me...but can I assume that if I was to use aliases, then I would be able to sneak past this problem? :) No. :) The reason is explained in the manual page (about INSERT) I cited. Bye, Benjamin. [...] As is described somewhere (http://www.mysql.com/doc/R/E/REPLACE.html), REPLACE mainly behaves like INSERT and therefore the target table may not appear in the SELECT clause (as described here: http://www.mysql.com/doc/I/N/INSERT_SELECT.html). [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP PLEASE - C API code help: UPDATE query using result from SELECT
Aha, much appreciated...your words therefore the target table may not appear in the SELECT clause have made it clear to me...but can I assume that if I was to use aliases, then I would be able to sneak past this problem? :) John :^) Benjamin Pflugmann wrote: Hi. As is described somewhere ( http://www.mysql.com/doc/R/E/REPLACE.html ), REPLACE mainly behaves like INSERT and therefore the target table may not appear in the SELECT clause (as described here: http://www.mysql.com/doc/I/N/INSERT_SELECT.html ). Sorry, but it seems you have to use a temporary table to store the intermediate result. Bye, Benjamin. On Tue, Oct 02, 2001 at 12:39:18PM +1000, [EMAIL PROTECTED] wrote: Hi, When I try and use the REPLACE function such as: REPLACE INTO table1 SELECT table2.ID, table2.Modified FROM table1 INNER JOIN table2 ON table1.Company=table2.Company; I get: ERROR 1066: Not unique table/alias: 'table1' [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP PLEASE - C API code help: UPDATE query using result from SELECT
Hi,
I believe that the query below is correct, but I do not believe that it is
possible in MySQL currently due to it not supporting sub-selects:
UPDATE table1 SET ID=table2.ID, Company=table2.Company,
Modified=table2.Modified FROM table1 INNER JOIN table2 ON
table1.Company=table2.Company WHERE table1.Modified='9';
The error produced was:
ERROR 1064: You have an error in your SQL syntax near 'FROM table1 INNER
JOIN table2 ON table1.Company=table2.Company WHERE table1.Modi' at line 1
If you can't tell from the SQL query above...what I am trying to do is
update data in table1 with data from table2...the two tables have pretty
much identical information, although table2 has updated data and table1
can't just be overwritten because it may have something newer than table2
again (this is only part of the entire process which involves two MySQL
servers and a MS SQL server :)
I've read that the only way to do it at the moment is using code such as C
or C++. Does anyone have any experience with this type of procedure?
My code so far is below...my coding knowledge is very weak and this was
obtained from a text:
#include stdlib.h
#include stdio.h
#include /usr/local/mysql/include/mysql/mysql.h
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
void display_row();
int main(int argc, char *argv[]) {
int res;
uint i = 0;
query = 0;
mysql_init(my_connection);
if (mysql_real_connect(my_connection, localhost, username,
password, database, 0, NULL, 0)) {
printf(Connection success\n);
res = mysql_query(my_connection, SELECT ID, Modified
FROM table1 WHERE Modified = 9);
if (res) {
printf(SELECT error: %s\n,
mysql_error(my_connection));
} else {
res_ptr = mysql_store_result(my_connection);
if (res_ptr) {
while ((sqlrow = mysql_fetch_row(res_ptr))) {
for (i=0; i
mysql_num_fields(res_ptr); i++)
printf(%s\n,sqlrow[i]);
printf(Fetched data...\n);
/*mysql_query(my_connection,
Some UPDATE code );*/
/*display_row();*/
}
if (mysql_errno(my_connection)) {
fprintf(stderr, Retrieve error:
%s\n, mysql_error(my_connection));
}
}
mysql_free_result(res_ptr);
}
mysql_close(my_connection);
} else {
fprintf(stderr, Connection failed\n);
if (mysql_errno(my_connection)) {
fprintf(stderr, Connection error %d:
%s\n, mysql_errno(my_connection), mysql_error(my_connection));
}
}
return EXIT_SUCCESS;
}
void display_row() {
unsigned int field_count;
field_count = 0;
while (field_count mysql_field_count(my_connection)) {
printf(%s , sqlrow[field_count]);
field_count++;
}
printf(\n);
}
This code works very well in obtaining the data from the tables, but I need
to get a procedure that will perform the UPDATE once the necessary data has
been obtained. In the section
/*mysql_query(my_connection, Some UPDATE code );*/
I have been able to get a static update to work, such as UPDATE table1 SET
Company = 'NewCompany' where Modified = 9, but I need it to input the
values which were obtained from the initial SELECT statement.
I've seen some incomplete code which defines a variable such as query[2048]
and then I assume somehow assigns a query to it which can then be used
similar to:
mysql_query(my_connection, query);
Can somebody please help me get this code completed, as it is going nowhere
awfully fast at the moment.
Thankyou,
John Mravunac
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: HELP PLEASE - C API code help: UPDATE query using result from SELECT
I am struggling with this myself right now. I posted a similar question to
this group earlier in the day and it must be a poser because no one has
responded.
I fear that the only way to do this with a single sql statement is using the
replace command:
Let's say you have 2 tables like this:
MainTbl
id int not null (primary key),
fld1int,
fld2int,
fld3int
UpdTbl
id int not null (primary key),
fld2int
And let's say that you have 20,000 recs in MainTbl and only 100 recs in
UpdTbl.
You want to join the 2 tables and flag the value from UpdTbl.fld2 into
MainTbl.fld2, right?
The best approach I have thought of is
REPLACE INTO MainTbl
SELECT MainTbl.id, MainTbl.fld1, UpdTbl.fld2, MainTbl.fld3
FROMMainTbl INNER JOIN UpdTbl ON MainTbl.id = UpdTbl.id;
I have not tried this yet but if I read the manual correctly, it should
work.
If anyone has a better solution, please fill us in.
Additionally, I would like ideas on how to work around MySQL's inability to
handle deletes like the following:
DELETE MainTbl
FROMMainTbl INNER JOIN DelTbl ON MainTbl.id = DelTbl.id;
-Original Message-
From: John Mravnuac [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 19, 2001 8:28 PM
To: [EMAIL PROTECTED]
Subject: HELP PLEASE - C API code help: UPDATE query using result from
SELECT
Hi,
I believe that the query below is correct, but I do not believe
that it is
possible in MySQL currently due to it not supporting sub-selects:
UPDATE table1 SET ID=table2.ID, Company=table2.Company,
Modified=table2.Modified FROM table1 INNER JOIN table2 ON
table1.Company=table2.Company WHERE table1.Modified='9';
The error produced was:
ERROR 1064: You have an error in your SQL syntax near 'FROM table1 INNER
JOIN table2 ON table1.Company=table2.Company WHERE table1.Modi' at line 1
If you can't tell from the SQL query above...what I am trying to do is
update data in table1 with data from table2...the two tables have pretty
much identical information, although table2 has updated data and table1
can't just be overwritten because it may have something newer than table2
again (this is only part of the entire process which involves two MySQL
servers and a MS SQL server :)
I've read that the only way to do it at the moment is using code
such as C
or C++. Does anyone have any experience with this type of procedure?
My code so far is below...my coding knowledge is very weak and this was
obtained from a text:
#include stdlib.h
#include stdio.h
#include /usr/local/mysql/include/mysql/mysql.h
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
void display_row();
int main(int argc, char *argv[]) {
int res;
uint i = 0;
query = 0;
mysql_init(my_connection);
if (mysql_real_connect(my_connection, localhost, username,
password, database, 0, NULL, 0)) {
printf(Connection success\n);
res = mysql_query(my_connection, SELECT ID, Modified
FROM table1 WHERE Modified = 9);
if (res) {
printf(SELECT error: %s\n,
mysql_error(my_connection));
} else {
res_ptr = mysql_store_result(my_connection);
if (res_ptr) {
while ((sqlrow =
mysql_fetch_row(res_ptr))) {
for (i=0; i
mysql_num_fields(res_ptr); i++)
printf(%s\n,sqlrow[i]);
printf(Fetched data...\n);
/*mysql_query(my_connection,
Some UPDATE code );*/
/*display_row();*/
}
if (mysql_errno(my_connection)) {
fprintf(stderr, Retrieve error:
%s\n, mysql_error(my_connection));
}
}
mysql_free_result(res_ptr);
}
mysql_close(my_connection);
} else {
fprintf(stderr, Connection failed\n);
if (mysql_errno(my_connection)) {
fprintf(stderr, Connection error %d:
%s\n, mysql_errno(my_connection), mysql_error(my_connection));
}
}
return EXIT_SUCCESS;
}
void display_row() {
unsigned int field_count;
field_count = 0;
while (field_count mysql_field_count(my_connection)) {
printf(%s , sqlrow[field_count]);
field_count++;
}
printf(\n);
}
This code works very well in obtaining the data from the tables,
but I need
to get a procedure that will perform
C API code help: UPDATE query using result from SELECT
Hi,
I believe that the query below is correct, but I do not believe that it is
possible in MySQL currently due to it not supporting sub-selects:
UPDATE table1 SET ID=table2.ID, Company=table2.Company,
Modified=table2.Modified FROM table1 INNER JOIN table2 ON
table1.Company=table2.Company WHERE table1.Modified='9';
The error produced was:
ERROR 1064: You have an error in your SQL syntax near 'FROM table1 INNER
JOIN table2 ON table1.Company=table2.Company WHERE table1.Modi' at line 1
If you can't tell from the SQL query above...what I am trying to do is
update data in table1 with data from table2...the two tables have pretty
much identical information, although table2 has updated data and table1
can't just be overwritten because it may have something newer than table2
again (this is only part of the entire process which involves two MySQL
servers and a MS SQL server :)
I've read that the only way to do it at the moment is using code such as C
or C++. Does anyone have any experience with this type of procedure?
My code so far is below...my coding knowledge is very weak and this was
obtained from a text:
#include stdlib.h
#include stdio.h
#include /usr/local/mysql/include/mysql/mysql.h
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
void display_row();
int main(int argc, char *argv[]) {
int res;
uint i = 0;
query = 0;
mysql_init(my_connection);
if (mysql_real_connect(my_connection, localhost, username,
password, database, 0, NULL, 0)) {
printf(Connection success\n);
res = mysql_query(my_connection, SELECT ID, Modified
FROM table1 WHERE Modified = 9);
if (res) {
printf(SELECT error: %s\n,
mysql_error(my_connection));
} else {
res_ptr = mysql_store_result(my_connection);
if (res_ptr) {
while ((sqlrow = mysql_fetch_row(res_ptr))) {
for (i=0; i
mysql_num_fields(res_ptr); i++)
printf(%s\n,sqlrow[i]);
printf(Fetched data...\n);
/*mysql_query(my_connection,
Some UPDATE code );*/
/*display_row();*/
}
if (mysql_errno(my_connection)) {
fprintf(stderr, Retrieve error:
%s\n, mysql_error(my_connection));
}
}
mysql_free_result(res_ptr);
}
mysql_close(my_connection);
} else {
fprintf(stderr, Connection failed\n);
if (mysql_errno(my_connection)) {
fprintf(stderr, Connection error %d:
%s\n, mysql_errno(my_connection), mysql_error(my_connection));
}
}
return EXIT_SUCCESS;
}
void display_row() {
unsigned int field_count;
field_count = 0;
while (field_count mysql_field_count(my_connection)) {
printf(%s , sqlrow[field_count]);
field_count++;
}
printf(\n);
}
This code works very well in obtaining the data from the tables, but I need
to get a procedure that will perform the UPDATE once the necessary data has
been obtained. In the section
/*mysql_query(my_connection, Some UPDATE code );*/
I have been able to get a static update to work, such as UPDATE table1 SET
Company = 'NewCompany' where Modified = 9, but I need it to input the
values which were obtained from the initial SELECT statement.
I've seen some incomplete code which defines a variable such as query[2048]
and then I assume somehow assigns a query to it which can then be used
similar to:
mysql_query(my_connection, query);
Can somebody please help me get this code completed, as it is going nowhere
awfully fast at the moment.
Thankyou,
John Mravunac
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
