Can I set many IP address with bind-address ? If not, how can do same thing ?
Hi, Can I set many IP address with bind-address ? If not, how can do same thing ? In documentation, I read this : ''' -bind-address=IP The IP address to bind to. ''' They don't say if I can use comma to separate two or many IP. Thanks for your help, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I set many IP address with bind-address ? If not, how can do same thing ?
KLEIN Stéphane wrote: Hi, Can I set many IP address with bind-address ? If not, how can do same thing ? In documentation, I read this : ''' -bind-address=IP The IP address to bind to. ''' They don't say if I can use comma to separate two or many IP. Thanks for your help, Stephane Hi Stephane, You can only specify one IP address to bind to. Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join vs subqueries
I'm not (yet) using sub-queries since the old version of MySQL were unable to handle them, then I was using 'join'. I wish to know if it's possibile to do all what I did with 'join' with subqueries. and which one is faster/better to use? for example it'd be possibile to 'translate' that using subselect? SELECT Esercente.Insegna, Terminale.TermId, Abilitazione.CodCarta, Circuito.Nome, Abilitazione.Data, Circuito.Tel FROM Esercente INNER JOIN Terminale ON Esercente.CodSia = Terminale.CodSia LEFT JOIN Abilitazione ON Terminale.TermId=Abilitazione.TermId LEFT JOIN Circuito ON Abilitazione.CodCarta=Circuito.CodCarta my db is that (and on attachment there is the pdf with the logical schema) CREATE DATABASE IF NOT EXISTS CentroServizi; USE CentroServizi; CREATE TABLE IF NOT EXISTS Esercente ( CodSia MEDIUMINT UNSIGNED NOT NULL, #Il CodSia e' un numero positivo a 7 # cifre; # MEDIUMINT UNSIGNED va da 0 a 16777215 Citta VARCHAR(20) NOT NULL, Insegna VARCHAR(50), PRIMARY KEY (CodSia) ) TYPE = InnoDB CREATE TABLE IF NOT EXISTS Terminale ( TermId MEDIUMINT UNSIGNED NOT NULL, # il TermId e' numero positivo a 6 cifre # MEDIUMINT UNSIGNED va da 0 a 16777215 CodSia MEDIUMINT UNSIGNED NOT NULL, Modello VARCHAR(20), PRIMARY KEY (TermId), INDEX CodSia (CodSia), FOREIGN KEY (CodSia) REFERENCES Esercente(CodSia) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; CREATE TABLE IF NOT EXISTS Circuito ( CodCarta TINYINT UNSIGNED NOT NULL, # il CodCarta e' un numero positivo a 2 # cifre # TINYINT UNSIGNED va da 0 a 255 Nome VARCHAR(20) NOT NULL, Tel VARCHAR(50), PRIMARY KEY (CodCarta) ) TYPE=INNODB; CREATE TABLE IF NOT EXISTS Abilitazione ( TermIdMEDIUMINT UNSIGNED NOT NULL, CodCarta TINYINT UNSIGNED NOT NULL, Data DATE NOT NULL, PRIMARY KEY (TermId, CodCarta), INDEX TermId (TermId), INDEX CodCarta (CodCarta), FOREIGN KEY (TermId) REFERENCES Terminale(TermId) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (CodCarta) REFERENCES Circuito(CodCarta) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; CREATE TABLE IF NOT EXISTS ChiaInt ( CallId INT UNSIGNED NOT NULL, # Si potranno gestire fino a 4294967295 # chiamate Operatore VARCHAR(20) NOT NULL, Chiamante MEDIUMINT UNSIGNED NOT NULL , # e' il CodSia Inizio DATETIME NOT NULL, FineDATETIME NULL, Manutentore VARCHAR(20), Motivo TEXT, # Stringa lunga al max 65KB PRIMARY KEY (CallId), INDEX Chiamante (Chiamante), FOREIGN KEY (Chiamante) REFERENCES Esercente(CodSia) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join vs subqueries
I cannot help you on specific performance timings -- but if the result is the same, the database engine can use whatever trick to retrieve them. That being said, a subquery/derived table could then be rewritten (internally) to a JOIN, for example. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com I'm not (yet) using sub-queries since the old version of MySQL were unable to handle them, then I was using 'join'. I wish to know if it's possibile to do all what I did with 'join' with subqueries. and which one is faster/better to use? for example it'd be possibile to 'translate' that using subselect? SELECT Esercente.Insegna, Terminale.TermId, Abilitazione.CodCarta, Circuito.Nome, Abilitazione.Data, Circuito.Tel FROM Esercente INNER JOIN Terminale ON Esercente.CodSia = Terminale.CodSia LEFT JOIN Abilitazione ON Terminale.TermId=Abilitazione.TermId LEFT JOIN Circuito ON Abilitazione.CodCarta=Circuito.CodCarta my db is that (and on attachment there is the pdf with the logical schema) CREATE DATABASE IF NOT EXISTS CentroServizi; USE CentroServizi; CREATE TABLE IF NOT EXISTS Esercente ( CodSia MEDIUMINT UNSIGNED NOT NULL, #Il CodSia e' un numero positivo a 7 # cifre; # MEDIUMINT UNSIGNED va da 0 a 16777215 Citta VARCHAR(20) NOT NULL, Insegna VARCHAR(50), PRIMARY KEY (CodSia) ) TYPE = InnoDB CREATE TABLE IF NOT EXISTS Terminale ( TermId MEDIUMINT UNSIGNED NOT NULL, # il TermId e' numero positivo a 6 cifre # MEDIUMINT UNSIGNED va da 0 a 16777215 CodSia MEDIUMINT UNSIGNED NOT NULL, Modello VARCHAR(20), PRIMARY KEY (TermId), INDEX CodSia (CodSia), FOREIGN KEY (CodSia) REFERENCES Esercente(CodSia) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; CREATE TABLE IF NOT EXISTS Circuito ( CodCarta TINYINT UNSIGNED NOT NULL, # il CodCarta e' un numero positivo a 2 # cifre # TINYINT UNSIGNED va da 0 a 255 Nome VARCHAR(20) NOT NULL, Tel VARCHAR(50), PRIMARY KEY (CodCarta) ) TYPE=INNODB; CREATE TABLE IF NOT EXISTS Abilitazione ( TermIdMEDIUMINT UNSIGNED NOT NULL, CodCarta TINYINT UNSIGNED NOT NULL, Data DATE NOT NULL, PRIMARY KEY (TermId, CodCarta), INDEX TermId (TermId), INDEX CodCarta (CodCarta), FOREIGN KEY (TermId) REFERENCES Terminale(TermId) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (CodCarta) REFERENCES Circuito(CodCarta) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; CREATE TABLE IF NOT EXISTS ChiaInt ( CallId INT UNSIGNED NOT NULL, # Si potranno gestire fino a 4294967295 # chiamate Operatore VARCHAR(20) NOT NULL, Chiamante MEDIUMINT UNSIGNED NOT NULL , # e' il CodSia Inizio DATETIME NOT NULL, FineDATETIME NULL, Manutentore VARCHAR(20), Motivo TEXT, # Stringa lunga al max 65KB PRIMARY KEY (CallId), INDEX Chiamante (Chiamante), FOREIGN KEY (Chiamante) REFERENCES Esercente(CodSia) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Windows) drop / create index and lock tables
Hi all, Say I have the following MyISAM table (example-ified) in a Windows-hosted DB: CREATE TABLE foo ( column1 CHAR(1), column2 CHAR(1), UNIQUE KEY `keyX`(`column1`)); I have to perform an update of the key to extend it to both columns (it's an example, ignore the content of the key), and want to ensure data integrity while I recreate it. The following is what I thought I had to do: LOCK TABLES foo WRITE; DROP INDEX `keyX` ON `foo`; CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`); UNLOCK TABLES; After much head-scratching due to Error Code : 1100 Table 'foo' was not locked with LOCK TABLES, I discovered that CREATE / DROP INDEX statements are mapped to equivalent ALTER TABLE statements. This, due to the way ALTER TABLE statements 'work' on windows, renders this code unusable as the DROP INDEX statement unlocks the table. Before the CREATE INDEX statement will work I then have to run UNLOCK TABLES, which also makes sense (I thought it was unlocked??). So - I can't lock the table whiel I drop then recreate the index, so what's the best way to do this? TIA, --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing 32 and 64 bit versions on Solaris
I have some applications that need a 32 bit library for Mysql. I would prefer to install the 64 bit version of MySQL but that doesnt seem to come with 32 bit libraries. Can I install both libraries. Any pointers would be appreciated Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning value after insert
Hi,
Is there a way of after a select statement having a return value for a
determined field ?
For example, after this:
INSERT INTO wl_users(name, email, password) VALUES('Deckard',
'[EMAIL PROTECTED]', 'blabla')
having the name returning without having to make a subsequent select.
Any help would be appreciated.
Best Regards,
Deckard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Windows) drop / create index and lock tables
Rob, seems like you want to ensure that no writes occur in between the drop index and create index statements, yes? It's not pretty, but you could stop the mysql service and start it back up with --skip-networking, then access it from localhost to perform your changes. If you have processes updating from localhost this won't be effective, though. Another thought might be to create your new index first, then drop the old, as in: CREATE UNIQUE INDEX `keyXX` ON `foo` (`column1`,`column2`); DROP INDEX `keyX` ON `foo`; The index having a different name should only be a problem if you've used optimizer hints in your SQL - some people do, some don't. Someone else may have a better thought. Dan On 10/3/06, Rob Desbois [EMAIL PROTECTED] wrote: Hi all, Say I have the following MyISAM table (example-ified) in a Windows-hosted DB: CREATE TABLE foo ( column1 CHAR(1), column2 CHAR(1), UNIQUE KEY `keyX`(`column1`)); I have to perform an update of the key to extend it to both columns (it's an example, ignore the content of the key), and want to ensure data integrity while I recreate it. The following is what I thought I had to do: LOCK TABLES foo WRITE; DROP INDEX `keyX` ON `foo`; CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`); UNLOCK TABLES; After much head-scratching due to Error Code : 1100 Table 'foo' was not locked with LOCK TABLES, I discovered that CREATE / DROP INDEX statements are mapped to equivalent ALTER TABLE statements. This, due to the way ALTER TABLE statements 'work' on windows, renders this code unusable as the DROP INDEX statement unlocks the table. Before the CREATE INDEX statement will work I then have to run UNLOCK TABLES, which also makes sense (I thought it was unlocked??). So - I can't lock the table whiel I drop then recreate the index, so what's the best way to do this? TIA, --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug?
Hello folks, I hope Greg is reading this list I use MySQL 5.0.24a from the FreeBSD ports, with libthr threading. I have a huge app makes alot of connections to MySQL server. The max I could reach is 1500 threads, even I made the max connection in my.cf 5k and I used kern.threads.max_threads_per_proc: to increase it to 5K. here is the my top in the box. 45101 mysql 1500 4 0 477M 166M sbwait 0 11:36 0.00% mysqld when it reachs 1500 I get this msg below Could not connect to db My server has dual xeon and 2 gig of ram. Any help would be appreciated. Thank you, -Abdullah
RE: Innodb Locks
Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question
Does anybody know how can I see what queries are currently being executed? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question
On Tuesday 03 October 2006 10:57, Feliks Shvartsburd wrote: Does anybody know how can I see what queries are currently being executed? From the mysql commandline, use show processlist; There is also a program called mytop which shows what's executing. -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question
Thanks, worked fine for me. Felix -Original Message- From: Chris Comparini [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:10 AM To: mysql@lists.mysql.com Subject: Re: Question On Tuesday 03 October 2006 10:57, Feliks Shvartsburd wrote: Does anybody know how can I see what queries are currently being executed? From the mysql commandline, use show processlist; There is also a program called mytop which shows what's executing. -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Locks
On 10/2/06, Robert DiFalco wrote: Is there a detailed source for when innodb creates row or table locks? The sourcecode. I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? It shouldn't from what you have described here. But might there be foreign key relation sbetween both tables? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Combined Primary Key and Auto Increment Primary Key
Hi all, I had somewhat of a performance question. I have an association table with 2 unique values which will always be selected by one of the values (never by id). That said, I'm wondering which would be a better gain, having this: CREATE TABLE association_sample ( `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, association_id1 INTEGER NOT NULL, association_id2 INTEGER NOT NULL )ENGINE=InnoDb; or CREATE TABLE association_sample ( association_id1 INTEGER NOT NULL, association_id2 INTEGER NOT NULL, PRIMARY KEY(association_id1,association_id2) )ENGINE=InnoDb; note that this table will mostly consist of table writes (updates, inserts, deletes). -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question
show processlist gives you an abbreviated list of queries. show full processlist gives you the full queries. -Original Message- From: Feliks Shvartsburd [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:57 PM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Question Does anybody know how can I see what queries are currently being executed? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Combined Primary Key and Auto Increment Primary Key
Chris, I'd opt for the first, but with an index on each of association_id1 and association_id2. I like always having an identity column to be able to remove or update an individual entry easily. But for speed, you'll want indexes on the other columns. I would either do no multi-column indexes, or do two - one each way - since you say you'll be selecting on one or the other. If you do one multi-column, but then select only on the 2nd column in the index, the index will do you no good. In most cases the overhead of additional indexes isn't too bad. Only in extreme cases does it end up being a major consideration, IMHO. HTH, Dan On 10/3/06, Chris White [EMAIL PROTECTED] wrote: Hi all, I had somewhat of a performance question. I have an association table with 2 unique values which will always be selected by one of the values (never by id). That said, I'm wondering which would be a better gain, having this: CREATE TABLE association_sample ( `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, association_id1 INTEGER NOT NULL, association_id2 INTEGER NOT NULL )ENGINE=InnoDb; or CREATE TABLE association_sample ( association_id1 INTEGER NOT NULL, association_id2 INTEGER NOT NULL, PRIMARY KEY(association_id1,association_id2) )ENGINE=InnoDb; note that this table will mostly consist of table writes (updates, inserts, deletes). -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question
Hi I have several problems. I'm using MySql 5 and it is running on Linux. When I'm trying to execute mysql -u root -p I get the following: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) I'm also not able to stop the server. When I run mysql.server stop it gives me some garbage. Please help. Thanks -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:25 AM To: Feliks Shvartsburd; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Question show processlist gives you an abbreviated list of queries. show full processlist gives you the full queries. -Original Message- From: Feliks Shvartsburd [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:57 PM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Question Does anybody know how can I see what queries are currently being executed? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Combined Primary Key and Auto Increment Primary Key
Unless I completely misunderstand your question, I don't see how the id field would ever be of use. You said you aren't going to be selecting on id, only by one or the other of association_id1 or association_id2. If you are really worried about the importance of inserts / updates / deletes, and not about retrievals, then don't index anything. A SELECT will have to serially access the table, while a write will have to shuffle the indices. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:20 PM To: mysql@lists.mysql.com Subject: Combined Primary Key and Auto Increment Primary Key Hi all, I had somewhat of a performance question. I have an association table with 2 unique values which will always be selected by one of the values (never by id). That said, I'm wondering which would be a better gain, having this: CREATE TABLE association_sample ( `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, association_id1 INTEGER NOT NULL, association_id2 INTEGER NOT NULL )ENGINE=InnoDb; or CREATE TABLE association_sample ( association_id1 INTEGER NOT NULL, association_id2 INTEGER NOT NULL, PRIMARY KEY(association_id1,association_id2) )ENGINE=InnoDb; note that this table will mostly consist of table writes (updates, inserts, deletes). -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Returning value after insert
You could use a stored procedure to do the INSERT and then return the
value from SELECT statement. For example,
DELIMITER $$;
DROP PROCEDURE IF EXISTS `test`.`spINSERTandSELECT`$$
CREATE PROCEDURE `test`.`spINSERTandSELECT` (IN strFirstName
VARCHAR(20),
IN strLastName VARCHAR(20))
BEGIN
INSERT INTO
usernames (FirstName, LastName)
VALUES
(strFirstName, strLastName);
SELECT CONCAT(strFirstName, , strLastName);
END$$
DELIMITER ;$$
Then call the stored procedure as follows:
mysql use test;
Database changed
mysql call spINSERTandSELECT(John, Doe);
++
| CONCAT(strFirstName, , strLastName) |
++
| John Doe |
++
1 row in set (0.06 sec)
Query OK, 0 rows affected (0.06 sec)
mysql
Hope this helps.
Randall Price
Microsoft Implementation Group
Secure Enterprise Computing Initiatives
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Deckard [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 03, 2006 11:40 AM
To: mysql@lists.mysql.com
Subject: Returning value after insert
Hi,
Is there a way of after a select statement having a return value for a
determined field ?
For example, after this:
INSERT INTO wl_users(name, email, password) VALUES('Deckard',
'[EMAIL PROTECTED]', 'blabla')
having the name returning without having to make a subsequent select.
Any help would be appreciated.
Best Regards,
Deckard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
No foreign key relationships. If I pull it into a temp table or a separate query that I then iterate through for all the updates on AnotherTable, then all works well. Odd. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:09 AM To: mysql@lists.mysql.com Subject: Re: Innodb Locks On 10/2/06, Robert DiFalco wrote: Is there a detailed source for when innodb creates row or table locks? The sourcecode. I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? It shouldn't from what you have described here. But might there be foreign key relation sbetween both tables? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question
It's possible that mysql couldn't create the SOCKet file... For emergency connection use: mysql -u root -h 127.0.0.1 -p Do not use localhost as this instructs the client to go through the socket... but if you say 127.0.0.1 the client will use TCP... Next... make sure that mysql can indeed create the socket under /tmp... check the permisions... and I must say this location is strange I have /var/lib/mysql/mysql.sock ... also check this out... To set it to another location use my.cnf file Good luck ! On 10/3/06, Feliks Shvartsburd [EMAIL PROTECTED] wrote: Hi I have several problems. I'm using MySql 5 and it is running on Linux. When I'm trying to execute mysql -u root -p I get the following: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) I'm also not able to stop the server. When I run mysql.server stop it gives me some garbage. Please help. Thanks -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:25 AM To: Feliks Shvartsburd; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Question show processlist gives you an abbreviated list of queries. show full processlist gives you the full queries. -Original Message- From: Feliks Shvartsburd [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:57 PM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Question Does anybody know how can I see what queries are currently being executed? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting numerically within a varchar
If I have the following strings in a varchar column: Piano Sonata 1 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 2 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel Is it possible to sort the records into the following order? Piano Sonata 1 - Brendel Piano Sonata 2 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Glitch in Query Optimizer
Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug?
Hello folks, I hope Greg is reading this list I use MySQL 5.0.24a from the FreeBSD ports, with libthr threading. I have a huge app makes alot of connections to MySQL server. The max I could reach is 1500 threads, even I made the max connection in my.cf 5k and I used kern.threads.max_threads_per_proc: to increase it to 5K. here is the my top in the box. 45101 mysql 1500 4 0 477M 166M sbwait 0 11:36 0.00% mysqld when it reachs 1500 I get this msg below Could not connect to db My server has dual xeon and 2 gig of ram. Any help would be appreciated. Thank you, -Abdullah
table_cache not read
All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Self References Indexes
Hi, Can anyone tell me if this is the correct syntax for a self reference Index ? -- ALTER TABLE `RPI_CTYPE` ADD FOREIGN KEY (`RPI_CT_REPLCID`) REFERENCES `RPI_CTYPE`(`RPI_CT_ID`); -- CREATE INDEX RPI_H_REPLCID_idxfk ON RPI_HOW (RPI_H_REPLCID); M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting last item
Hello, What is the best approach to select the last row of a table ? Thank you. Warm regards, Deckard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Glitch in Query Optimizer
Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help ERROR 2002
I don't know what happened. I was doing a very big query and now I'm not Able to access mysql. This is the error I get when I try to use command: Mysql -u root -p ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) How do I fix this? This is affecting my websites as it cannot locate my databases. Thanks, Ngim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Locks
There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting last item
When i need to do it i use something like this: select * from table order by column desc limit 1; Deckard [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hello, What is the best approach to select the last row of a table ? Thank you. Warm regards, Deckard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help ERROR 2002
Okay all seems to be fine now. All I did was restart the server for the 2nd time And now it seems to connect to mysql. Whew! I was starting to panic there. -Original Message- From: nngau [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 3:42 PM To: mysql@lists.mysql.com Subject: Help ERROR 2002 I don't know what happened. I was doing a very big query and now I'm not Able to access mysql. This is the error I get when I try to use command: Mysql -u root -p ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) How do I fix this? This is affecting my websites as it cannot locate my databases. Thanks, Ngim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table_cache not read
Robert, I recall a similar issue on Windows a couple of months ago. The problem there was the presence of multiple ini files scattered about. Check your system for multiple ini files and consolidate remove the extras, perhaps. HTH, Dan On 10/3/06, Robert Coggins [EMAIL PROTECTED] wrote: All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure Security Question
When creating a stored procedure, you can set the sql security characteristic to either definer or invoker. As an example, I have a stored procedure that does a select from a table, and an application user (appuser) that calls the stored procedure. If the sql security is set to invoker, then I have to give appuser both select and execute privileges. If the sql security is set to definer, then the definer needs select privileges and appuser only needs execute. What I'd like to be able to do is to give appuser the execute privilege and not have to give any privileges on the underlying tables to the definer. Is this possible? We do almost 100% of our work through stored procedures. It would be a lot easier to manage just the execute privilege. Are there reasons why this is not a good idea? This is how we manage security with our other DBMS and it's worked quite well, but it doesn't have the definer/invoker characteristic for stored procs either. Any suggestions about how to manage users/privileges would be appreciated. Donna
Re: RE: Glitch in Query Optimizer
Have you tried using the USE/IGNORE/FORCE INDEX optimizer hints? http://dev.mysql.com/doc/refman/5.0/en/join.html Sometimes, MySQL's optimize just doesn't make the best choice. Somewhat rare in my experience but it happens. HTH, Dan On 10/3/06, Robert DiFalco [EMAIL PROTECTED] wrote: Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: Glitch in Query Optimizer
Thanks, I had seen that but I don't have a lot of flexibility for adding database specific extensions on a query by query basis. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:30 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: RE: Glitch in Query Optimizer Have you tried using the USE/IGNORE/FORCE INDEX optimizer hints? http://dev.mysql.com/doc/refman/5.0/en/join.html Sometimes, MySQL's optimize just doesn't make the best choice. Somewhat rare in my experience but it happens. HTH, Dan On 10/3/06, Robert DiFalco [EMAIL PROTECTED] wrote: Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating indexes on a table already containing data
Hi all, I've got a database that has a few thousand rows, I've noticed that some of the search queries (especially the large ones) are taking some time. Im looking at adding indexes to my tables in order to speed up the data retrieval. My question is as follows: At this point in time if I add the indexes to the various tables should I see immediate results in the query times or do I have to wait for new information to enter the database (only new data gets indexed?) When does the data actually get indexed? Is it when its inserted or continually when regards are inserted or updated in the database? Thanks in advance. -- Angelo Zanetti Systems developer *Telephone:* +27 (021) 469 1052 *Mobile:* +27 (0) 72 441 3355 *Fax:*+27 (0) 86 681 5885 * Web:* http://www.zlogic.co.za *E-Mail:* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting numerically within a varchar
James, it is possible, if your number is always in the same relative position in the string (it is in the sample data you posted below). If it moves around a lot, you may be better off establishing some kind of sortorder column and populating it with your favorite scripting language. Actually that could conceivably be done in SQL too. Here's a sample of one way to do it, using substrings based on ' ' (space) characters, casting the result to an unsigned int, then sorting on that column. SELECT dataline, CAST( SUBSTRING_INDEX( SUBSTRING_INDEX(dataline, ' ', 3), ' ', -1) AS UNSIGNED) AS nr FROM test ORDER BY nr; HTH, Dan On 10/3/06, James Eaton [EMAIL PROTECTED] wrote: If I have the following strings in a varchar column: Piano Sonata 1 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 2 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel Is it possible to sort the records into the following order? Piano Sonata 1 - Brendel Piano Sonata 2 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating indexes on a table already containing data
Angelo, results should be (nearly) immediate. When you add an index, MySQL creates an index for the existing data in your table. Later, when data is added/updated/deleted, the index is updated simultaneously. With a few thousand rows, you should be able to get by adding a few indexes where they seem to make sense. As your database grows, it might be worth your while to go more in-depth with MySQL optimization. The online manual has a good section at: http://dev.mysql.com/doc/refman/5.0/en/optimization.html and I also highly recommend Jeremy Zawodny's book High Performance MySQL. Dan On 10/3/06, Angelo Zanetti [EMAIL PROTECTED] wrote: Hi all, I've got a database that has a few thousand rows, I've noticed that some of the search queries (especially the large ones) are taking some time. Im looking at adding indexes to my tables in order to speed up the data retrieval. My question is as follows: At this point in time if I add the indexes to the various tables should I see immediate results in the query times or do I have to wait for new information to enter the database (only new data gets indexed?) When does the data actually get indexed? Is it when its inserted or continually when regards are inserted or updated in the database? Thanks in advance. -- Angelo Zanetti Systems developer *Telephone:* +27 (021) 469 1052 *Mobile:* +27 (0) 72 441 3355 *Fax:*+27 (0) 86 681 5885 * Web:* http://www.zlogic.co.za *E-Mail:* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating indexes on a table already containing data
In the last episode (Oct 03), Angelo Zanetti said: I've got a database that has a few thousand rows, I've noticed that some of the search queries (especially the large ones) are taking some time. Im looking at adding indexes to my tables in order to speed up the data retrieval. My question is as follows: At this point in time if I add the indexes to the various tables should I see immediate results in the query times or do I have to wait for new information to enter the database (only new data gets indexed?) When does the data actually get indexed? Is it when its inserted or continually when regards are inserted or updated in the database? When you add an index, Mysql builds a complete index of your table immediately. For a few thousand rows it should take under a minute. The index will automatically be updated as you insert rows or change existing ones. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Glitch in Query Optimizer
On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco wrote: Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? Search the docs for FORCE INDEX () and IGNORE INDEX () if you don't want to rely on MySQL's cleverness. bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting numerically within a varchar
James, That wasn't too easy to figure out. But this will work: select * from Table1 order by substring_index(Music_Title,' ',2),-- Extracts first 2 words 0+Substring_Index(Substring_index(Music_Title,'-',1),' ',-2), -- Extracts the number substring_index(Music_Title,' ',-1) -- Extracts the last word Mike At 02:42 PM 10/3/2006, you wrote: If I have the following strings in a varchar column: Piano Sonata 1 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 2 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel Is it possible to sort the records into the following order? Piano Sonata 1 - Brendel Piano Sonata 2 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating indexes on a table already containing data
Dan Nelson wrote: In the last episode (Oct 03), Angelo Zanetti said: I've got a database that has a few thousand rows, I've noticed that some of the search queries (especially the large ones) are taking some time. Im looking at adding indexes to my tables in order to speed up the data retrieval. My question is as follows: At this point in time if I add the indexes to the various tables should I see immediate results in the query times or do I have to wait for new information to enter the database (only new data gets indexed?) When does the data actually get indexed? Is it when its inserted or continually when regards are inserted or updated in the database? When you add an index, Mysql builds a complete index of your table immediately. For a few thousand rows it should take under a minute. The index will automatically be updated as you insert rows or change existing ones. thanks Dan and Dan. I will have a look at the mysql site. However I checked and there are 600 rows approx in the database. The query im running has 3 subselect statements and takes quite a while, would you say that indexing wouldn't help as there arent that many rows in the database currently? And therefore consider rewriting parts of the statement? I will add the indexes as good measuse anyway. TIA -- Angelo Zanetti Systems developer *Telephone:* +27 (021) 469 1052 *Mobile:* +27 (0) 72 441 3355 *Fax:*+27 (0) 86 681 5885 * Web:* http://www.zlogic.co.za *E-Mail:* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table_cache not read
Dan, Thanks for the reply... I wondered if this might be the case myself. However, I made a change the the variable query_cache_size in the same ini and it registered correctly after the service restart. Or, are you saying MySQL might be reading from multiple ini files? Well, either way in the mean time I am going to search for additional ini files that may contain the offending variable value. Thanks! Robert Coggins Dan Buettner wrote: Robert, I recall a similar issue on Windows a couple of months ago. The problem there was the presence of multiple ini files scattered about. Check your system for multiple ini files and consolidate remove the extras, perhaps. HTH, Dan On 10/3/06, Robert Coggins [EMAIL PROTECTED] wrote: All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table_cache not read
Well, I scoured the HDDs and I was unable to find additional my.ini files. Any other thoughts! Thanks again for your help! Robert Coggins wrote: Dan, Thanks for the reply... I wondered if this might be the case myself. However, I made a change the the variable query_cache_size in the same ini and it registered correctly after the service restart. Or, are you saying MySQL might be reading from multiple ini files? Well, either way in the mean time I am going to search for additional ini files that may contain the offending variable value. Thanks! Robert Coggins Dan Buettner wrote: Robert, I recall a similar issue on Windows a couple of months ago. The problem there was the presence of multiple ini files scattered about. Check your system for multiple ini files and consolidate remove the extras, perhaps. HTH, Dan On 10/3/06, Robert Coggins [EMAIL PROTECTED] wrote: All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table_cache not read
Well, I scoured the HDDs and I was unable to find additional my.ini files. Any other thoughts! Thanks again for your help! Robert Coggins wrote: Dan, Thanks for the reply... I wondered if this might be the case myself. However, I made a change the the variable query_cache_size in the same ini and it registered correctly after the service restart. Or, are you saying MySQL might be reading from multiple ini files? Well, either way in the mean time I am going to search for additional ini files that may contain the offending variable value. Thanks! Robert Coggins Dan Buettner wrote: Robert, I recall a similar issue on Windows a couple of months ago. The problem there was the presence of multiple ini files scattered about. Check your system for multiple ini files and consolidate remove the extras, perhaps. HTH, Dan On 10/3/06, Robert Coggins [EMAIL PROTECTED] wrote: All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating indexes on a table already containing data
At 05:42 PM 10/3/2006, you wrote: Dan Nelson wrote: In the last episode (Oct 03), Angelo Zanetti said: I've got a database that has a few thousand rows, I've noticed that some of the search queries (especially the large ones) are taking some time. Im looking at adding indexes to my tables in order to speed up the data retrieval. My question is as follows: At this point in time if I add the indexes to the various tables should I see immediate results in the query times or do I have to wait for new information to enter the database (only new data gets indexed?) When does the data actually get indexed? Is it when its inserted or continually when regards are inserted or updated in the database? When you add an index, Mysql builds a complete index of your table immediately. For a few thousand rows it should take under a minute. The index will automatically be updated as you insert rows or change existing ones. thanks Dan and Dan. I will have a look at the mysql site. However I checked and there are 600 rows approx in the database. The query im running has 3 subselect statements and takes quite a while, would you say that indexing wouldn't help as there arent that many rows in the database currently? And therefore consider rewriting parts of the statement? I will add the indexes as good measuse anyway. TIA Use an Explain in front of your Select statement in the MySQL gui to see which indexes it is using. Example: explain select * from table1 where cust_no=123 order by date_sold; Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many enum values?
how many is too many? i have a field with 21 possible values. each of the values are only 2 or 3 letter strings, but that seems like a lot, would it be faster/more efficient to put them in a separate table and just join?
