Re: Multi select Query help...
2012/03/01 19:56 -0800, Don Wieland I do not get the same results. Am I missing something? Hopefully something simple ;-) O, you are. You do not want GROUP_CONCAT in the subquery. It gives you the comma-separated string whereto you referred, which, as far as the IN goes, is only one string for comparing for equality. You want the IDs separate, not joined into one string. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Multi select Query help...
Appreciate a little guidance here: Background: I have an invoicing system. Invoices are generated and (invoice and Invoice Items) and Payments are generated (Payments and Payment Items). Payment items are amount of the Payment Total allocated to payoff open invoices. So I may have 3 open invoice for $100 each and I may generate one payment for $300 with 3 payment items for $100 each to pay off those 3 open invoices. In most cases, clients will pay their own invoices off, but in rare cases another client will pay an invoice for that client (ie... spouse, parent, etc...) My client want me to some how display when the payee (or one of the payees - there can be multiple) of the invoice IS NOT the same client as the invoice being paid. So I need to display a result that show a comma delineated string of payees OMITTING the invoice's client_id. I only want to show a result if one or more of the payees are different than the invoice's client_id. So now with the mySQL queries that are working: First of all, the client_id of the invoice I am querying on is 251719. query 1 = select group_concat(payment_id) from tl_trans_pmt_items where inv_id = 1033911 This produces a string 1033882,1021630,1021632. These are parent Payment records which have the payee client_ids. So if I run a query: query 2 = select group_concat(client_id) FROM tl_transactions WHERE transaction_id IN (1033882,1021630,1021632) AND client_id != 251719 This produces a string 251711,251713. These are the client_ids of the Payment records OMITTING the Invoice's client_id So far this works fine. Now where I run into issues is where I try to combine these queries together: query 3 = select group_concat(client_id) FROM tl_transactions WHERE transaction_id IN ((select group_concat(payment_id) from tl_trans_pmt_items where inv_id = 1033911)) AND client_id != 251719 I do not get the same results. Am I missing something? Hopefully something simple ;-) Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Script to mail output of select query
Dear all, I have prepared a simple script that shows the database, tables size in the Database server as :- Please check the attachment for the script output. Now I just want to mail the output of my script to some persons e-mail-ID Also, I want to do some calculations and provide the information of how much increase data after 1 day daily. Please guide me how to do this. Thanks best Regards, Adarsh Sharma status.sh Description: application/shellscript -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Script to mail output of select query
- Original Message - From: Adarsh Sharma adarsh.sha...@orkash.com Please check the attachment for the script output. Thanks for your password :-) Now I just want to mail the output of my script to some persons e-mail-ID Assuming you run this from crontab, just set MAILTO=per...@domain.ext right before the script execution line. Also, I want to do some calculations and provide the information of how much increase data after 1 day daily. Uhh. Munin? Cacti? Output CSV-formatted data and append to a file, then process using a spreadsheet? Insert the data into a table in your DB and play with that? Plenty of ways to do that, all depends on what you want and need. The key is that you're going to have to keep historical data for that somewhere. Strictly speaking, also not on topic here :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complex Select Query
Alternatively, you could just code a recursive function to do the same instead of doing it in SQL or stored procs. On Tue, Aug 24, 2010 at 11:01 AM, Victor Subervi victorsube...@gmail.comwrote: On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley peter.braw...@earthlink.netwrote: What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? It's a tree. See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and http://www.artfulsoftware.com/infotree/treequeryperformance.pdf. How do I cut down the tree? That stuff is *way* too complex for my needs. I just thought I'd make my presentation of data a little cleaner but frankly it ain't worth going through all that learning and experimentation to do it. If there isn't another way I'll just forget about it. Any other suggestions would be nice. TIA. Victor - -Original Message- From: Victor Subervi victorsube...@gmail.com Sent: Aug 24, 2010 1:14 PM To: mysql@lists.mysql.com Subject: Complex Select Query Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | ID | int(4) | NO | PRI | NULL| auto_increment | | Category| int(3) | YES | | NULL|| | Item| varchar(20) | YES | UNI | NULL|| | Description | varchar(255) | YES | | NULL|| | UOM | varchar(20) | YES | | NULL|| | Cost| float(7,2) | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) | NO | PRI | NULL| auto_increment | | Category | varchar(20) | YES | UNI | NULL|| | Parent | varchar(20) | YES | | NULL|| +--+-+--+-+-++ What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? TIA, Victor
Complex Select Query
Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | ID | int(4) | NO | PRI | NULL| auto_increment | | Category| int(3) | YES | | NULL|| | Item| varchar(20) | YES | UNI | NULL|| | Description | varchar(255) | YES | | NULL|| | UOM | varchar(20) | YES | | NULL|| | Cost| float(7,2) | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) | NO | PRI | NULL| auto_increment | | Category | varchar(20) | YES | UNI | NULL|| | Parent | varchar(20) | YES | | NULL|| +--+-+--+-+-++ What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? TIA, Victor
Re: Complex Select Query
What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? It's a tree. See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and http://www.artfulsoftware.com/infotree/treequeryperformance.pdf. PB - -Original Message- From: Victor Subervi victorsube...@gmail.com Sent: Aug 24, 2010 1:14 PM To: mysql@lists.mysql.com Subject: Complex Select Query Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | ID | int(4) | NO | PRI | NULL| auto_increment | | Category| int(3) | YES | | NULL|| | Item| varchar(20) | YES | UNI | NULL|| | Description | varchar(255) | YES | | NULL|| | UOM | varchar(20) | YES | | NULL|| | Cost| float(7,2) | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) | NO | PRI | NULL| auto_increment | | Category | varchar(20) | YES | UNI | NULL|| | Parent | varchar(20) | YES | | NULL|| +--+-+--+-+-++ What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complex Select Query
On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley peter.braw...@earthlink.netwrote: What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? It's a tree. See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and http://www.artfulsoftware.com/infotree/treequeryperformance.pdf. How do I cut down the tree? That stuff is *way* too complex for my needs. I just thought I'd make my presentation of data a little cleaner but frankly it ain't worth going through all that learning and experimentation to do it. If there isn't another way I'll just forget about it. Any other suggestions would be nice. TIA. Victor - -Original Message- From: Victor Subervi victorsube...@gmail.com Sent: Aug 24, 2010 1:14 PM To: mysql@lists.mysql.com Subject: Complex Select Query Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | ID | int(4) | NO | PRI | NULL| auto_increment | | Category| int(3) | YES | | NULL|| | Item| varchar(20) | YES | UNI | NULL|| | Description | varchar(255) | YES | | NULL|| | UOM | varchar(20) | YES | | NULL|| | Cost| float(7,2) | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) | NO | PRI | NULL| auto_increment | | Category | varchar(20) | YES | UNI | NULL|| | Parent | varchar(20) | YES | | NULL|| +--+-+--+-+-++ What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? TIA, Victor
SELECT query question
Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna
Re: SELECT query question
select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. -- João Cândido de Souza Neto SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS Fone: (0XX41) 3033-3636 - JS www.siens.com.br Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SELECT query question
Should be more efficient to do something like: SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name' UNION SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name' UNION SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name' -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re: SELECT query question select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. -- João Cândido de Souza Neto SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS Fone: (0XX41) 3033-3636 - JS www.siens.com.br Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT query question
There are many ways to get the same result. hehehehe Gavin Towey gto...@ffn.com escreveu na mensagem news:30b3df511cec5c4dae4d0d290504753413956dc...@aaa.pmgi.local... Should be more efficient to do something like: SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name' UNION SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name' UNION SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name' -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re: SELECT query question select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql select query
Can you show the CREATE TABLE for your REF_SEQ table? The explain output says using where which means that MySQL will have to post-filter rows after the storage engine retrieves them. It also means the query may benefit from different/better indexing. On Mon, Jul 13, 2009 at 12:04 AM, TianJing tianj...@genomics.org.cn wrote: i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as ABTGDSDFSGFDG etc. 2009/7/13 Darryle Steplight dstepli...@gmail.com Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTian jingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jingtian.seu...@gmail.com -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com msn%3atianjing...@hotmail.com msn%3atianjing...@hotmail.com msn%253atianjing...@hotmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: mysql select query
the REF_SEQ is defined below, the col DNA_SEQ is a string such as ATGCGGTTA, | REF_SEQ | CREATE TABLE `REF_SEQ` ( `SEQ_ID` int(11) NOT NULL auto_increment, `REF_ID` int(11) NOT NULL, `START_POSITION` int(11) NOT NULL, `END_POSITION` int(11) NOT NULL, `DNA_SEQ` text, `DNA_QUALITY` text, PRIMARY KEY (`SEQ_ID`), KEY `index_ref_start` (`REF_ID`,`START_POSITION`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | i create a index on cols REF_ID and START_POSITION, i also use analyze table REF_SEQ to optimization the query, and now the explain output is: mysql explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+--+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+--+-+--++-+ | 1 | SIMPLE | REF_SEQ | ALL | NULL | NULL | NULL| NULL | 219728 | Using where | ++-+-+--+---+--+-+--++-+ 2009/7/13 Johnny Withers joh...@pixelated.net Can you show the CREATE TABLE for your REF_SEQ table? The explain output says using where which means that MySQL will have to post-filter rows after the storage engine retrieves them. It also means the query may benefit from different/better indexing. On Mon, Jul 13, 2009 at 12:04 AM, TianJing tianj...@genomics.org.cnwrote: i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as ABTGDSDFSGFDG etc. 2009/7/13 Darryle Steplight dstepli...@gmail.com Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTian jingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives:
Re: mysql select query
I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql only uses the left-most column of this index. Drop and re-add this key only defined as INDEX idx_ref_start(start_position) and see if that helps. Your explain you sent this time is not even using the index. In your previous explain output, mysql said the key_len is 5. Since both columns in this key are INT (4-bytes), it says it's only using the left-most column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else can explain this. I'd redefine the index to only use the a single column, then define a new index on REF_ID if you use that in JOINs. On Mon, Jul 13, 2009 at 9:07 AM, TianJing tianj...@genomics.org.cn wrote: the REF_SEQ is defined below, the col DNA_SEQ is a string such as ATGCGGTTA, | REF_SEQ | CREATE TABLE `REF_SEQ` ( `SEQ_ID` int(11) NOT NULL auto_increment, `REF_ID` int(11) NOT NULL, `START_POSITION` int(11) NOT NULL, `END_POSITION` int(11) NOT NULL, `DNA_SEQ` text, `DNA_QUALITY` text, PRIMARY KEY (`SEQ_ID`), KEY `index_ref_start` (`REF_ID`,`START_POSITION`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | i create a index on cols REF_ID and START_POSITION, i also use analyze table REF_SEQ to optimization the query, and now the explain output is: mysql explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+--+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+--+-+--++-+ | 1 | SIMPLE | REF_SEQ | ALL | NULL | NULL | NULL| NULL | 219728 | Using where | ++-+-+--+---+--+-+--++-+ 2009/7/13 Johnny Withers joh...@pixelated.net Can you show the CREATE TABLE for your REF_SEQ table? The explain output says using where which means that MySQL will have to post-filter rows after the storage engine retrieves them. It also means the query may benefit from different/better indexing. On Mon, Jul 13, 2009 at 12:04 AM, TianJing tianj...@genomics.org.cnwrote: i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as ABTGDSDFSGFDG etc. 2009/7/13 Darryle Steplight dstepli...@gmail.com Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTian jingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in
Re: mysql select query
sorry for my careless,the sql should be select * from REF_SEQ where REF_ID = 3 and START_POSITION between 3 and 803; the explain output is : mysql explain select * from REF_SEQ where REF_ID = 3 and START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+--+-+ | 1 | SIMPLE | REF_SEQ | range | index_ref_start | index_ref_start | 8 | NULL | 2408 | Using where | ++-+-+---+-+-+-+--+--+-+ in this sql,the index is on REF_ID and START_POSITION, the rows in the output is more less than that index_POS on START_POSITION and index_ref on REF_ID. 2009/7/13 Johnny Withers joh...@pixelated.net I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql only uses the left-most column of this index. Drop and re-add this key only defined as INDEX idx_ref_start(start_position) and see if that helps. Your explain you sent this time is not even using the index. In your previous explain output, mysql said the key_len is 5. Since both columns in this key are INT (4-bytes), it says it's only using the left-most column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else can explain this. I'd redefine the index to only use the a single column, then define a new index on REF_ID if you use that in JOINs. On Mon, Jul 13, 2009 at 9:07 AM, TianJing tianj...@genomics.org.cn wrote: the REF_SEQ is defined below, the col DNA_SEQ is a string such as ATGCGGTTA, | REF_SEQ | CREATE TABLE `REF_SEQ` ( `SEQ_ID` int(11) NOT NULL auto_increment, `REF_ID` int(11) NOT NULL, `START_POSITION` int(11) NOT NULL, `END_POSITION` int(11) NOT NULL, `DNA_SEQ` text, `DNA_QUALITY` text, PRIMARY KEY (`SEQ_ID`), KEY `index_ref_start` (`REF_ID`,`START_POSITION`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | i create a index on cols REF_ID and START_POSITION, i also use analyze table REF_SEQ to optimization the query, and now the explain output is: mysql explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+--+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+--+-+--++-+ | 1 | SIMPLE | REF_SEQ | ALL | NULL | NULL | NULL| NULL | 219728 | Using where | ++-+-+--+---+--+-+--++-+ 2009/7/13 Johnny Withers joh...@pixelated.net Can you show the CREATE TABLE for your REF_SEQ table? The explain output says using where which means that MySQL will have to post-filter rows after the storage engine retrieves them. It also means the query may benefit from different/better indexing. On Mon, Jul 13, 2009 at 12:04 AM, TianJing tianj...@genomics.org.cn wrote: i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as ABTGDSDFSGFDG etc. 2009/7/13 Darryle Steplight dstepli...@gmail.com Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJing tianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
Re: mysql select query
It looks like MySQL is using both columns in the key for that query, since the key_len is 8, but for some reason it says it is still using where. What happens when you only select these fields: seq_id, ref_id, start_position, end_position? Does the query speed up? I had a table that had some TEXT columns defined and I found when I selected every column excep the TEXT column the query ran faster. On Mon, Jul 13, 2009 at 9:45 AM, TianJing tianj...@genomics.org.cn wrote: sorry for my careless,the sql should be select * from REF_SEQ where REF_ID = 3 and START_POSITION between 3 and 803; the explain output is : mysql explain select * from REF_SEQ where REF_ID = 3 and START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+--+-+ | 1 | SIMPLE | REF_SEQ | range | index_ref_start | index_ref_start | 8 | NULL | 2408 | Using where | ++-+-+---+-+-+-+--+--+-+ in this sql,the index is on REF_ID and START_POSITION, the rows in the output is more less than that index_POS on START_POSITION and index_ref on REF_ID. 2009/7/13 Johnny Withers joh...@pixelated.net I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql only uses the left-most column of this index. Drop and re-add this key only defined as INDEX idx_ref_start(start_position) and see if that helps. Your explain you sent this time is not even using the index. In your previous explain output, mysql said the key_len is 5. Since both columns in this key are INT (4-bytes), it says it's only using the left-most column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else can explain this. I'd redefine the index to only use the a single column, then define a new index on REF_ID if you use that in JOINs. On Mon, Jul 13, 2009 at 9:07 AM, TianJing tianj...@genomics.org.cn wrote: the REF_SEQ is defined below, the col DNA_SEQ is a string such as ATGCGGTTA, | REF_SEQ | CREATE TABLE `REF_SEQ` ( `SEQ_ID` int(11) NOT NULL auto_increment, `REF_ID` int(11) NOT NULL, `START_POSITION` int(11) NOT NULL, `END_POSITION` int(11) NOT NULL, `DNA_SEQ` text, `DNA_QUALITY` text, PRIMARY KEY (`SEQ_ID`), KEY `index_ref_start` (`REF_ID`,`START_POSITION`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | i create a index on cols REF_ID and START_POSITION, i also use analyze table REF_SEQ to optimization the query, and now the explain output is: mysql explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+--+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+--+-+--++-+ | 1 | SIMPLE | REF_SEQ | ALL | NULL | NULL | NULL| NULL | 219728 | Using where | ++-+-+--+---+--+-+--++-+ 2009/7/13 Johnny Withers joh...@pixelated.net Can you show the CREATE TABLE for your REF_SEQ table? The explain output says using where which means that MySQL will have to post-filter rows after the storage engine retrieves them. It also means the query may benefit from different/better indexing. On Mon, Jul 13, 2009 at 12:04 AM, TianJing tianj...@genomics.org.cn wrote: i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as ABTGDSDFSGFDG etc. 2009/7/13 Darryle Steplight dstepli...@gmail.com Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJing tianj...@genomics.org.cn wrote: thanks
Re: mysql select query
yes,it is more faster that i select every cols except the TEXT col,but unfortunately i need the TEXT cols for next step. 2009/7/14 Johnny Withers joh...@pixelated.net It looks like MySQL is using both columns in the key for that query, since the key_len is 8, but for some reason it says it is still using where. What happens when you only select these fields: seq_id, ref_id, start_position, end_position? Does the query speed up? I had a table that had some TEXT columns defined and I found when I selected every column excep the TEXT column the query ran faster. On Mon, Jul 13, 2009 at 9:45 AM, TianJing tianj...@genomics.org.cnwrote: sorry for my careless,the sql should be select * from REF_SEQ where REF_ID = 3 and START_POSITION between 3 and 803; the explain output is : mysql explain select * from REF_SEQ where REF_ID = 3 and START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+--+-+ | 1 | SIMPLE | REF_SEQ | range | index_ref_start | index_ref_start | 8 | NULL | 2408 | Using where | ++-+-+---+-+-+-+--+--+-+ in this sql,the index is on REF_ID and START_POSITION, the rows in the output is more less than that index_POS on START_POSITION and index_ref on REF_ID. 2009/7/13 Johnny Withers joh...@pixelated.net I see that index_ref_start is defined on Ref_Id and Start_Position. Mysql only uses the left-most column of this index. Drop and re-add this key only defined as INDEX idx_ref_start(start_position) and see if that helps. Your explain you sent this time is not even using the index. In your previous explain output, mysql said the key_len is 5. Since both columns in this key are INT (4-bytes), it says it's only using the left-most column, REF_ID. I'm not sure why it says 5 and not 4, maybe someone else can explain this. I'd redefine the index to only use the a single column, then define a new index on REF_ID if you use that in JOINs. On Mon, Jul 13, 2009 at 9:07 AM, TianJing tianj...@genomics.org.cn wrote: the REF_SEQ is defined below, the col DNA_SEQ is a string such as ATGCGGTTA, | REF_SEQ | CREATE TABLE `REF_SEQ` ( `SEQ_ID` int(11) NOT NULL auto_increment, `REF_ID` int(11) NOT NULL, `START_POSITION` int(11) NOT NULL, `END_POSITION` int(11) NOT NULL, `DNA_SEQ` text, `DNA_QUALITY` text, PRIMARY KEY (`SEQ_ID`), KEY `index_ref_start` (`REF_ID`,`START_POSITION`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | i create a index on cols REF_ID and START_POSITION, i also use analyze table REF_SEQ to optimization the query, and now the explain output is: mysql explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+--+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+--+-+--++-+ | 1 | SIMPLE | REF_SEQ | ALL | NULL | NULL | NULL| NULL | 219728 | Using where | ++-+-+--+---+--+-+--++-+ 2009/7/13 Johnny Withers joh...@pixelated.net Can you show the CREATE TABLE for your REF_SEQ table? The explain output says using where which means that MySQL will have to post-filter rows after the storage engine retrieves them. It also means the query may benefit from different/better indexing. On Mon, Jul 13, 2009 at 12:04 AM, TianJing tianj...@genomics.org.cn wrote: i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as ABTGDSDFSGFDG etc. 2009/7/13 Darryle Steplight dstepli...@gmail.com Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJing tianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5
mysql select query
Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing
Re: mysql select query
1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql select query
thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com msn%3atianjing...@hotmail.com
Re: mysql select query
You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql select query
sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jingtian.seu...@gmail.com -- Tianjing
Re: mysql select query
Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jingtian.seu...@gmail.com -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql select query
i do not use text for start_postion,i use int for it. the only col which defined to text is characters such as ABTGDSDFSGFDG etc. 2009/7/13 Darryle Steplight dstepli...@gmail.com Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jingtian.seu...@gmail.com -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com msn%3atianjing...@hotmail.com
Re: If condition in select query / insert /update
Hi, mysql create table t(i int); mysql insert into t values(1),(2),(3); mysql select i, if(i = 1, 'low', 'high') from t order by i; +--+---+ | i| if(i = 1, 'low', 'high') | +--+---+ |1 | low | |2 | high | |3 | high | +--+---+ 3 rows in set (0.06 sec) Take a look at the documentation for IF(), http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html All the best, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Mon, 2009-05-18 at 09:55 +0530, bharani kumar wrote: Hi all , Can u give one example query , Which contain the IF condition , Because here before am not used the IF and all , Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: If condition in select query / insert /update
Can u tell me , assume if i use If in the query , then i reduce performance , Any idea On Mon, May 18, 2009 at 3:19 PM, Janek Bogucki janek.bogu...@studylink.comwrote: Hi, mysql create table t(i int); mysql insert into t values(1),(2),(3); mysql select i, if(i = 1, 'low', 'high') from t order by i; +--+---+ | i| if(i = 1, 'low', 'high') | +--+---+ |1 | low | |2 | high | |3 | high | +--+---+ 3 rows in set (0.06 sec) Take a look at the documentation for IF(), http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html All the best, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Mon, 2009-05-18 at 09:55 +0530, bharani kumar wrote: Hi all , Can u give one example query , Which contain the IF condition , Because here before am not used the IF and all , Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bharanikumariyer...@gmail.com -- உங்கள் நண்பன் பரணி குமார் Regards B.S.Bharanikumar POST YOUR OPINION http://bharanikumariyerphp.site88.net/bharanikumar/
If condition in select query / insert /update
Hi all , Can u give one example query , Which contain the IF condition , Because here before am not used the IF and all , Thanks -- உங்கள் நண்பன் பரணி குமார் Regards B.S.Bharanikumar POST YOUR OPINION http://bharanikumariyerphp.site88.net/bharanikumar/
Re: Solved Select query locks tables in Innodb
Just to close this off. Baron was correct in that the core problem was a bug in MySQL (I was using version 5.0.37.) There are some references to this bug in the MySQL bug stuff but they claim to have eliminated it in 5.0.30... apparently not. I ungraded to version 5.1.32 and the original problem disappeared and the selects behave as one would expect. Many thanks to all who offered advice. Carl - Original Message - From: Perrin Harkins per...@elem.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Friday, March 13, 2009 1:40 PM Subject: Re: Select query locks tables in Innodb 2009/3/12 Carl c...@etrak-plus.com: I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. What's the relationship you're implying between memory and locking? Multi-version concurrency doesn't necessarily mean the older versions that are being read from have to be entirely in memory. InnoDB will lock on a query that doesn't use an index. It shouldn't lock on a SELECT query, regardless of the indexes involved. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Negated SELECT query
3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: Negated SELECT query
On Tue, Mar 17, 2009 at 12:42 PM, BobSharp bobsh...@ntlworld.com wrote: These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Negated SELECT query
SELECT Employees.* FROM Employees LEFT JOIN Assets ON Employess.EmployeeID = Assets.EmployeeID WHERE Assets.EmployeeID IS NULL The one for assets with no maintenance is similar. The point is the left join above produces in its output all rows from the Employees table regardless of whether anything matches in the assets table. By selecting only rows where the foreign key field in the assets table is null gives you the employees having no assets. John Bonnett -Original Message- From: BobSharp [mailto:bobsh...@ntlworld.com] Sent: Wednesday, 18 March 2009 3:13 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Negated SELECT query 3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Negated SELECT query
Thanks for that,worked through and found that this gives the correct result ... --- Employee No Assets --- SELECT DISTINCT e.employeeID AS eID, concat(e.firstname, , e.lastname) AS eName FROM employees e LEFT JOIN assets a ON e.employeeID = a.employeeID WHERE e.employeeID IS NULL ORDER BY e.employeeID --- Employee No History --- SELECT DISTINCT a.assetID AS aCode, LEFT(a.assetdescription,60) AS aTitle, c.assetcategory AS cCategory FROM assets a LEFT JOIN maintenance m ON m.assetID = a.assetID LEFT JOIN assetcategories c ON a.assetcategoryID = c.assetcategoryID WHERE m.assetID IS NULL ORDER BY a.assetID Cheers - Original Message - From: Bonnett, John john.bonn...@vision.zeiss.com To: bobsh...@ntlworld.com; mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Sent: Tuesday, March 17, 2009 10:59 PM Subject: RE: Negated SELECT query SELECT Employees.* FROM Employees LEFT JOIN Assets ON Employess.EmployeeID = Assets.EmployeeID WHERE Assets.EmployeeID IS NULL The one for assets with no maintenance is similar. The point is the left join above produces in its output all rows from the Employees table regardless of whether anything matches in the assets table. By selecting only rows where the foreign key field in the assets table is null gives you the employees having no assets. John Bonnett -Original Message- From: BobSharp [mailto:bobsh...@ntlworld.com] Sent: Wednesday, 18 March 2009 3:13 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Negated SELECT query 3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bobsh...@ntlworld.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.237 / Virus Database: 270.11.18/2008 - Release Date: 03/17/09 16:25:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
2009/3/12 Carl c...@etrak-plus.com: I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. What's the relationship you're implying between memory and locking? Multi-version concurrency doesn't necessarily mean the older versions that are being read from have to be entirely in memory. InnoDB will lock on a query that doesn't use an index. It shouldn't lock on a SELECT query, regardless of the indexes involved. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
Brent, After a delay while I was busy killing alligators, I did as you suggested (added a composite index of date and organization_serial on journal_entry_master... in the spirit of your suggestion, anyway.) The results were interesting: 1. In my test environment, I could not force a locked file even though I opened the dates up to cover 2+ years and changed to an organization that had more records. The 'Explain' is attached as temp1.txt. You will note that it starts with 100,000+ records while the eventual set of records for the report is 60,000 because the 100,000+ number includes some journmal entries for refund/void/etc. transactions which we have no interest in. 2. I tried various combinations of indexes but couldn't seem to get any better than the composite one on the journal_entry_master. I did not check whether the other options would produce locked files. I am now going to put this into production and see if it will actually fly. I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. Thanks for all your help and Baron's suggestions also. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not very unique, on average it will only narrow down the table to 1/687 of it's full size. This is probably the source of your locking problem and where you want to focus. InnoDB will lock on a query that doesn't use an index. It would have to lock every record anyway, so why not lock the table? 36,000 records still may be too large of a result set to do record versioning. But, optimizing your query is the only way to go. Your date_effective is a lot more granular, so you may want to focus on that. If you do a lot of these types of searches, you can try creating a compound index on organization_serial+date_effective. CREATE INDEX (org_date) ON journal_entry_master(organization_serial,date_effective) MySQL would/should then use that query, which will narrow things down quicker and better. It shouldn't have to try to do versioning on 56,000 records while it tries to get the subset of that (36,000). Brent On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote: Brent, The query returns about 36,000 rows. The 56,000 rows from the journal_entry_master table is all the entries for organization 16 (they span more than the dates I have asked for.) SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary index (auto-increment), 687 for the organization_serial (the one I am using), 18 for the organization_shift_start (I tried this before, i.e., starting with the organization_shift, but it quickly got mired down) and 777,000+ for the date_effective. If I understand correctly, you have suggested using the date index. The difficulty is the data contains many organizations and so the date range query returns 163,000+ rows. Also, I would expect scaling a query where I had to programatically cut it up would 1) be difficult and 2) wouldn't really solve the problem but would rather just shorten the time of the locks. I am not suggesting that I might not end up there, only hoping for a better solution. Thanks for all your insight and feel free to suggest away. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Wednesday, March 04, 2009 4:23 PM Subject: Re: Select query locks tables in Innodb Is the result of the query returning 56,000+ rows? How many rows are you expecting to be returned once the query is finished running? Your date range is over a year. You may actually get much better performance (and avoid locking) by running more queries with a narrower date range and linking them through a UNION. It's using the organization index rather than the date index. I don't know your dataset, but typically you want your query to use the date index since that narrows down the data set better. You can run SHOW INDEX FROM journal_entry_master to see the distribution of your data in the index. The cardinality column will indicate the uniqueness of your data. The higher the number, the more unique values. Brent 2009/3/4 Carl c...@etrak-plus.com: Under stress (having transaction entered), the query shows that it is still locking the tables. I rewrote the query and tested it step by step but could not tell whether tyhe partially complete query was locking tables because it ran so fast. However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. Does anyone
Re: Select query locks tables in Innodb
The nice thing about InnnoDB is that it won't have to access the data portion of the file if it doesn't have to. So if all the information you are retrieving is contained in an index, it only accesses the index to get the information it needs. The data portion is never access, and thus never locked. Something like this is probably going on. All the information it needs for the 100,000 records is contained in the index, the the data portion is never accessed until it needs to retrieve the 60,000 records. That's a simplistic overview of what could be going on. But it sounds like your issue has been resolved. Interesting, your temp1 attached file shows mysql switched from using the org_date index to the organization index. Brent Baisley 2009/3/12 Carl c...@etrak-plus.com: Brent, After a delay while I was busy killing alligators, I did as you suggested (added a composite index of date and organization_serial on journal_entry_master... in the spirit of your suggestion, anyway.) The results were interesting: 1. In my test environment, I could not force a locked file even though I opened the dates up to cover 2+ years and changed to an organization that had more records. The 'Explain' is attached as temp1.txt. You will note that it starts with 100,000+ records while the eventual set of records for the report is 60,000 because the 100,000+ number includes some journmal entries for refund/void/etc. transactions which we have no interest in. 2. I tried various combinations of indexes but couldn't seem to get any better than the composite one on the journal_entry_master. I did not check whether the other options would produce locked files. I am now going to put this into production and see if it will actually fly. I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. Thanks for all your help and Baron's suggestions also. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not very unique, on average it will only narrow down the table to 1/687 of it's full size. This is probably the source of your locking problem and where you want to focus. InnoDB will lock on a query that doesn't use an index. It would have to lock every record anyway, so why not lock the table? 36,000 records still may be too large of a result set to do record versioning. But, optimizing your query is the only way to go. Your date_effective is a lot more granular, so you may want to focus on that. If you do a lot of these types of searches, you can try creating a compound index on organization_serial+date_effective. CREATE INDEX (org_date) ON journal_entry_master(organization_serial,date_effective) MySQL would/should then use that query, which will narrow things down quicker and better. It shouldn't have to try to do versioning on 56,000 records while it tries to get the subset of that (36,000). Brent On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote: Brent, The query returns about 36,000 rows. The 56,000 rows from the journal_entry_master table is all the entries for organization 16 (they span more than the dates I have asked for.) SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary index (auto-increment), 687 for the organization_serial (the one I am using), 18 for the organization_shift_start (I tried this before, i.e., starting with the organization_shift, but it quickly got mired down) and 777,000+ for the date_effective. If I understand correctly, you have suggested using the date index. The difficulty is the data contains many organizations and so the date range query returns 163,000+ rows. Also, I would expect scaling a query where I had to programatically cut it up would 1) be difficult and 2) wouldn't really solve the problem but would rather just shorten the time of the locks. I am not suggesting that I might not end up there, only hoping for a better solution. Thanks for all your insight and feel free to suggest away. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Wednesday, March 04, 2009 4:23 PM Subject: Re: Select query locks tables in Innodb Is the result of the query returning 56,000+ rows? How many rows are you expecting to be returned once the query is finished running? Your date range is over a year. You may actually get much better performance (and avoid locking) by running more queries with a narrower date range and linking them through a UNION. It's using the organization index rather than the date index. I don't know your dataset, but typically you want your query to use the date index since
Re: Select query locks tables in Innodb
I really appreciate the time you have taken to help me with this problem.
I will be out of the office until around 1:00PM and will try your
suggestions.
I did attach a copy of the query but it may have been stripped somewhere
along the line so I have placed it in line below.
select *
from payment_to_fee_link_budget_account_detail_link, journal_entry_master,
journal_entry_type,
payment_to_fee_link_event, payment_to_fee_link, fees, fees_event,
fees_budget_account_detail_link, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial,
transaction_event, receipt_master, budget_account_detail,
budget_account_detail as ptfl_budget_account_detail, budget_account_master
where journal_entry_master.organization_serial = 16
and journal_entry_master.date_effective = '2008-01-01'
and journal_entry_master.date_effective '2009-03-31'
and journal_entry_type.journal_entry_type_serial =
journal_entry_master.journal_entry_type_serial
and
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial =
journal_entry_master.journal_entry_master_serial
and payment_to_fee_link_budget_account_detail_link.date_effective =
'2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective
'2009-03-31'
and payment_to_fee_link_event.payment_to_fee_link_event_serial =
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and transaction_event.transaction_event_serial =
payment_to_fee_link_event.transaction_event_serial
and fees.fees_serial = payment_to_fee_link.fees_serial
and transactions.transactions_serial = fees.transactions_serial
and person.person_serial = transactions.person_serial
and receipt_master.receipt_serial = transaction_event.receipt_serial
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial =
fees_event.fees_event_serial
and budget_account_detail.budget_account_detail_serial =
fees_budget_account_detail_link.budget_account_detail_serial
and ptfl_budget_account_detail.budget_account_detail_serial =
payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
and budget_account_master.budget_account_serial =
budget_account_detail.budget_account_serial
and budget_account_master.budget_account_type_serial = 5001
TIA,
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STATUS while this is going on. And use mysqladmin debug and
check the error log. Then put those in some pastebin and send us the
link. And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running. There's not a lot I can do
to really help you with this other than assume that you are wrong :)
Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.
5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release. You might be surprised at how
much that changes things.
Baron
On Wed, Mar 4, 2009 at 3:33 PM, Carl c...@etrak-plus.com wrote:
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten
out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message - From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you
Re: Select query locks tables in Innodb
Thanks to all of you.
The key was the 107488 rows. I restructured the query so that it started
with something smaller and it 1) runs faster (I'm guessing the reduced use
of temp space) and 2) did not seem to cause any locking problems (I will
test this under load today.)
I have attached a copy of the query which has been simplified in a couple of
ways (I don't really want every field from every row selected from every
table.) Also, the constants like organization_serial (16) and dates are
variables in the real version.
The explain now shows:
idtable typepossible_keys
keylenref rows
1organization_shiftrefPRIMARY, organizationorganization
4const5
1organization_shift_start ref PRIMARY, organization_shift
organization_shift4 organization_shift_serial295
1journal_entry_masterrefPRIMARY, organization_shift_start
organization_shift_start 5 organization_shift_start_serial 52
Note that it now starts with 5 row, expands to 295 rows, etc. not the
100,000+ from before.
Again, thanks for all your help.
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
wrote:
A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just locking the table.
InnoDB does not do lock escalation a la SQL Server etc. I'd look at
Perrin's suggestions, I think they are likely to be the problem.
More importantly, what is the query? :-)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com
# sales from collections
(select *
from organization_shift, organization_shift_start, transaction_event,payment_to_fee_link_event,payment_to_fee_link, payment_to_fee_link_budget_account_detail_link,fees_budget_account_detail_link, fees_event, budget_account_detail,
payments, budget_account_detail as ptfl_budget_account_detail, budget_account_master, journal_entry_master, journal_entry_type, receipt_master, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial
where organization_shift.organization_serial = 16
and organization_shift_start.organization_shift_serial =
organization_shift.organization_shift_serial
and organization_shift_start.date_effective = '2008-01-01'
and organization_shift_start.date_effective '2009-03-31'
#$P!{organizationShiftStartQuery}
and journal_entry_master.organization_shift_start_serial =
organization_shift_start.organization_shift_start_serial
and receipt_master.receipt_serial =
transaction_event.receipt_serial
and transactions.transactions_serial =
transaction_event.transactions_serial
and transactions.organization_serial = organization_shift.organization_serial
#$P!{itemSerials}
and person.person_serial = transactions.person_serial
and payment_to_fee_link_event.transaction_event_serial =
transaction_event.transaction_event_serial
and
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
= payment_to_fee_link_event.payment_to_fee_link_event_serial
and
payment_to_fee_link_budget_account_detail_link.cash_basis_reporting_flag = 'Y'
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and payments.payments_serial =
payment_to_fee_link.payments_serial
and payment_to_fee_link_budget_account_detail_link.date_effective
= '2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective
'2009-03-31'
and (payments.payment_type_code_serial in (
1,2,3,4,5,8,24,6,7,12,13,23,25 )# 1,2,3,4,5,8,24,6,7,12,13,23,25
or
payment_to_fee_link_budget_account_detail_link.description='Apply available
credit to customer accounts receivable')
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial =
fees_event.fees_event_serial
and
fees_budget_account_detail_link.budget_account_detail_serial
Re: Select query locks tables in Innodb
Under stress (having transaction entered), the query shows that it is still locking the tables. I rewrote the query and tested it step by step but could not tell whether tyhe partially complete query was locking tables because it ran so fast. However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. Does anyone have any ideas? TIA, Carl - Original Message - From: Baron Schwartz ba...@xaprb.com To: Brent Baisley brentt...@gmail.com Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com select * from payment_to_fee_link_budget_account_detail_link, journal_entry_master, journal_entry_type, payment_to_fee_link_event, payment_to_fee_link, fees, fees_event, fees_budget_account_detail_link, person, transactions left join regs on regs.transactions_serial = transactions.transactions_serial, transaction_event, receipt_master, budget_account_detail, budget_account_detail as ptfl_budget_account_detail, budget_account_master where journal_entry_master.organization_serial = 16 and journal_entry_master.date_effective = '2008-01-01' and journal_entry_master.date_effective '2009-03-31' and journal_entry_type.journal_entry_type_serial = journal_entry_master.journal_entry_type_serial and payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial = journal_entry_master.journal_entry_master_serial and payment_to_fee_link_budget_account_detail_link.date_effective = '2008-01-01' and payment_to_fee_link_budget_account_detail_link.date_effective '2009-03-31' and payment_to_fee_link_event.payment_to_fee_link_event_serial = payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial and payment_to_fee_link.payment_to_fee_link_serial = payment_to_fee_link_event.payment_to_fee_link_serial and transaction_event.transaction_event_serial = payment_to_fee_link_event.transaction_event_serial and fees.fees_serial = payment_to_fee_link.fees_serial and transactions.transactions_serial = fees.transactions_serial and person.person_serial = transactions.person_serial and receipt_master.receipt_serial = transaction_event.receipt_serial and fees_event.fees_serial = payment_to_fee_link.fees_serial and ( fees_event.transaction_event_description_serial = 13 or fees_event.transaction_event_description_serial = 2 ) and fees_budget_account_detail_link.fees_event_serial = fees_event.fees_event_serial and budget_account_detail.budget_account_detail_serial = fees_budget_account_detail_link.budget_account_detail_serial and ptfl_budget_account_detail.budget_account_detail_serial = payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial and budget_account_master.budget_account_serial = budget_account_detail.budget_account_serial and budget_account_master.budget_account_type_serial = 5001 temp.XLS Description: MS-Excel spreadsheet 1 SIMPLE journal_entry_masterref PRIMARY,organization,journal_entry_type_serial,date_effective organization 4 const 56926 Using where 1 SIMPLE journal_entry_type eq_ref PRIMARY PRIMARY 4 PRODUCTION.journal_entry_master.journal_entry_type_serial 1 1 SIMPLE payment_to_fee_link_budget_account_detail_link ref journal_entry,budget_account_detail_serial,event,date_effective journal_entry 4 PRODUCTION.journal_entry_master.journal_entry_master_serial 1 Using where 1 SIMPLE ptfl_budget_account_detail eq_ref PRIMARY PRIMARY 4 PRODUCTION.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial 1 1 SIMPLE
Re: Select query locks tables in Innodb
2009/3/4 Carl c...@etrak-plus.com: However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. I don't know what that is, but I think you'd better look at something closer to the bone, like SHOW INNODB STATUS. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. EXPLAIN isn't really relevant to table locking. InnoDB tables should never let readers block writers for a simple SELECT. Does anyone have any ideas? Did you check that your tables are InnoDB? Are you running some kind of crazy isolation level? - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
One more note. Perrin asked if I was using any select... for update. The answer is no, neither in the select query that seems to be locking the tables nor in the queries that are processing transactions. Surprisingly, one of the tables that reports being locked is never accessed in the report query. It is a foreign key on one of the files that is used. TIA, Carl - Original Message - From: Baron Schwartz ba...@xaprb.com To: Brent Baisley brentt...@gmail.com Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
I did check that all tables are Innodb. I was using the Navicat Server Monitor because I know that when I see the monitor reporting a status of locked during an attempted query, that user comes to a complete halt until the lock is cleared (usually by the bad query finishing.) I will check the isolation level but I believe it is whatever was set out of the box (five years ago.) Thanks, Carl - Original Message - From: Perrin Harkins per...@elem.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, March 04, 2009 1:49 PM Subject: Re: Select query locks tables in Innodb 2009/3/4 Carl c...@etrak-plus.com: However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. I don't know what that is, but I think you'd better look at something closer to the bone, like SHOW INNODB STATUS. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. EXPLAIN isn't really relevant to table locking. InnoDB tables should never let readers block writers for a simple SELECT. Does anyone have any ideas? Did you check that your tables are InnoDB? Are you running some kind of crazy isolation level? - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
Carl, Locked status in SHOW PROCESSLIST and a table being locked are different. There is a bug in MySQL that shows Locked status for queries accessing InnoDB tables in some cases. What version of MySQL are you using? The table is not really locked, you're just seeing that as a side effect of whatever's really happening. Baron On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote: I did check that all tables are Innodb. I was using the Navicat Server Monitor because I know that when I see the monitor reporting a status of locked during an attempted query, that user comes to a complete halt until the lock is cleared (usually by the bad query finishing.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
Carl, Locked status in SHOW PROCESSLIST and a table being locked are different. There is a bug in MySQL that shows Locked status for queries accessing InnoDB tables in some cases. What version of MySQL are you using? The table is not really locked, you're just seeing that as a side effect of whatever's really happening. Baron On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote: I did check that all tables are Innodb. I was using the Navicat Server Monitor because I know that when I see the monitor reporting a status of locked during an attempted query, that user comes to a complete halt until the lock is cleared (usually by the bad query finishing.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.
Baron
On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:
I did check that all tables are Innodb.
I was using the Navicat Server Monitor because I know that when I see the
monitor reporting a status of locked during an attempted query, that user
comes to a complete halt until the lock is cleared (usually by the bad
query
finishing.)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STATUS while this is going on. And use mysqladmin debug and
check the error log. Then put those in some pastebin and send us the
link. And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running. There's not a lot I can do
to really help you with this other than assume that you are wrong :)
Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.
5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release. You might be surprised at how
much that changes things.
Baron
On Wed, Mar 4, 2009 at 3:33 PM, Carl c...@etrak-plus.com wrote:
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message - From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.
Baron
On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:
I did check that all tables are Innodb.
I was using the Navicat Server Monitor because I know that when I see the
monitor reporting a status of locked during an attempted query, that user
comes to a complete halt until the lock is cleared (usually by the bad
query
finishing.)
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select query locks tables in Innodb
I have been wrestling with this problem for a couple of weeks and have been unable to find a solution. The MySQL version is 5.0.37 and it is running on a Slackware Linux 11 box. The problem: A query that is selecting data for a report locks the files that it accesses forcing users who are attempting to enter transactions to wait until the select query is finished. The query is sizable so I have not included it here (I can if that would be helpful.) Explain shows (abbreviated): id select_typetabletypepossible keys key_len refrows Extra 1SIMPLE transactions ref PRIMARY,person,organization 4const107448 * 1SIMPLE person eq_ref PRIMARY 4person_serial1 1SIMPLE regs ref transaction 4transactions_serial 1 1SIMPLE transaction_event refPRIMARY, transaction, receipt 4transactions_serial1 1SIMPLE receipt_masterref PRIMARY 4receipt_serial1 The 107448 rows are the transactions for the organization I am reporting. The person is linked directly to the transaction. During the select query, the person table is locked thereby stopping updates to any person in the table. I have always thought a select is only a read and would, therefore, not lock any tables. Anyone have any ideas? TIA, Carl
Re: Select query locks tables in Innodb
A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. Is that how many records you want to return? That seems like a lot. Maybe reworking your query may help. Heck, post the sizeable query. You've been spending weeks on it. Brent Baisley On Tue, Mar 3, 2009 at 10:53 AM, Carl c...@etrak-plus.com wrote: I have been wrestling with this problem for a couple of weeks and have been unable to find a solution. The MySQL version is 5.0.37 and it is running on a Slackware Linux 11 box. The problem: A query that is selecting data for a report locks the files that it accesses forcing users who are attempting to enter transactions to wait until the select query is finished. The query is sizable so I have not included it here (I can if that would be helpful.) Explain shows (abbreviated): id select_type table type possible keys key_len ref rows Extra 1 SIMPLE transactions ref PRIMARY,person,organization 4 const 107448 * 1 SIMPLE person eq_ref PRIMARY 4 person_serial 1 1 SIMPLE regs ref transaction 4 transactions_serial 1 1 SIMPLE transaction_event ref PRIMARY, transaction, receipt 4 transactions_serial 1 1 SIMPLE receipt_master ref PRIMARY 4 receipt_serial 1 The 107448 rows are the transactions for the organization I am reporting. The person is linked directly to the transaction. During the select query, the person table is locked thereby stopping updates to any person in the table. I have always thought a select is only a read and would, therefore, not lock any tables. Anyone have any ideas? TIA, Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 10:53 AM, Carl c...@etrak-plus.com wrote: A query that is selecting data for a report locks the files that it accesses forcing users who are attempting to enter transactions to wait until the select query is finished. Is it an INSERT INTO...SELECT FROM? Those lock. Also, have you verified that each table you think is InnoDB really is? Do a SHOW CREATE TABLE on them. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
long select query result as as query string on another select statment
Hi, - (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name='abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks
Re: long select query result as as query string on another select statment
what is the issue ur facing. Any syntax error or something else. trying usin IN instead of = On 9/26/08, Madan Thapa [EMAIL PROTECTED] wrote: Hi, - (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name='abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks
Re: long select query result as as query string on another select statment
-- --- (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name='abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks On Fri, Sep 26, 2008 at 4:29 PM, Ananda Kumar [EMAIL PROTECTED] wrote: what is the issue ur facing. Any syntax error or something else. trying usin IN instead of = ==/ The error is : The query could not be executed, I tried with IN instead of = too. My guess is , double brackets is causing some syntax error in : select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please note: SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name=' abc.com') it works and give the id number, it seems I am not using the correct syntax for a ))( double bracket ) in the above query. Please advise. Thanks
Re: long select query result as as query string on another select statment
Hi, ok i got it working. it was a typo(lol), i missed from in the initial select statment Wrong ### select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Correct ## select * from db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Thanks On Fri, Sep 26, 2008 at 5:18 PM, Madan Thapa [EMAIL PROTECTED]wrote: -- --- (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name=' abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks On Fri, Sep 26, 2008 at 4:29 PM, Ananda Kumar [EMAIL PROTECTED] wrote: what is the issue ur facing. Any syntax error or something else. trying usin IN instead of = ==/ The error is : The query could not be executed, I tried with IN instead of = too. My guess is , double brackets is causing some syntax error in : select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please note: SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name=' abc.com') it works and give the id number, it seems I am not using the correct syntax for a ))( double bracket ) in the above query. Please advise. Thanks
Re: Select Query
On Fri, May 23, 2008 at 11:20 PM, Velen [EMAIL PROTECTED] wrote: Hi, I wanted to know when doing a select query how is it executed : If there is 1000 records with price10, 3000 records with flag='Y' and the table contains 200,000 records. Select code, description, price, flag from products where flag='Y' and price10 Select code, description, price, flag from products where price10 and flag='Y' Which one of the query will be faster? In query 1, will mysql sort the list for flag='Y' then from the list find price'10'? Regards, Velen There should be no difference in quey execution. If there is an index on either column with good cardinality, then that index will probably be used to eliminate records first. If you are on mysql 5.0+ then multiple index may be used (merge index). After this happens each individual row will need to be examined, which will be expensive depending on the number or rows left after using the index. EXPLAIN and EXPLAIN EXTENDED are your friends for questions like this. At some point I need to dig into the mysql source to gain a better understanding of what is going on... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remove temporary table from SELECT query
Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
Insert Select query problem
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Thanks
Re: Insert Select query problem
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty = totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
cheers,
Jay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remove temporary table from SELECT query
Hi , ORDER BY, will always use some temporary table for doing sort operation. For that matter, any group function, like min,max,group by,order by will use temporary table before displaying the final results. Regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: neither of those have fixed the issue.. I can create a sample database if anyone wants it to be of some help. Mike On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: Also, its not good to set sort_buffer_size=256M, as this much of memory will get allocated to each session, and if u have more than 10 connections at any point of time then it will more than 2GB RAM will get allocated and system will be slow. I feel sort_buffer_size=1MB should be good in my.cnf You need to increase this parameter only at session level and not at my.cnf level. set sort_buffer_size=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto: [EMAIL PROTECTED] ] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6
Re: remove temporary table from SELECT query
at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
Re: remove temporary table from SELECT query
Also, its not good to set sort_buffer_size=256M, as this much of memory will get allocated to each session, and if u have more than 10 connections at any point of time then it will more than 2GB RAM will get allocated and system will be slow. I feel sort_buffer_size=1MB should be good in my.cnf You need to increase this parameter only at session level and not at my.cnflevel. set sort_buffer_size=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] ] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com ', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM
Re: remove temporary table from SELECT query
neither of those have fixed the issue.. I can create a sample database if anyone wants it to be of some help. Mike On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: Also, its not good to set sort_buffer_size=256M, as this much of memory will get allocated to each session, and if u have more than 10 connections at any point of time then it will more than 2GB RAM will get allocated and system will be slow. I feel sort_buffer_size=1MB should be good in my.cnf You need to increase this parameter only at session level and not at my.cnf level. set sort_buffer_size=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto: [EMAIL PROTECTED] ] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default ' i1.photoblog.com ', `notes` longtext
Re: Insert Select query problem
Thanks Jay,
I had to make a change to the first part of the query to get the results that I
wanted but your suggestion was definitely what I needed to get to the solution.
Thanks again.
For those that are interested, here's the final solution,
INSERT INTO purchase (Source, Item, Qty)
SELECT 1, totals.Item,if((totals.TotQty -r.Qty)0,r.qty, totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
UNION All
SELECT 0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
On 8/10/07 at 12:33 PM, in message [EMAIL PROTECTED], Jay Pipes [EMAIL
PROTECTED] wrote:
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty = totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
cheers,
Jay
remove temporary table from SELECT query
I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+-+---+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+-+---+--+-+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+-+---+--+-+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
RE: remove temporary table from SELECT query
It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
struggling with select query
Guys, Below is a select query which I'm stuggling with, so I'd be grateful for any help you could give me. select distinct TraderPersonalInfo.TraderID,PlatformMap.PlatformID from TraderPersonalInfo,Locations,PlatformMap,Platforms where (TraderPersonalInfo.TraderID = PlatformMap.TraderID) and (PlatformMap.PlatformID = Platforms.PlatformID) and PlatformMap.PlatformID = 2 or PlatformMap.PlatformID = 4; The query above produces the following output: +--++ | TraderID | PlatformID | +--++ |5 | 4 | |4 | 4 | |1 | 4 | |2 | 4 | |3 | 4 | |6 | 4 | |7 | 4 | |9 | 4 | | 10 | 4 | | 11 | 4 | | 20 | 4 | | 13 | 4 | | 14 | 4 | | 15 | 4 | | 19 | 4 | | 17 | 4 | | 18 | 4 | | 23 | 4 | | 22 | 4 | | 24 | 4 | |2 | 2 | |5 | 2 | |6 | 2 | |7 | 2 | |9 | 2 | | 10 | 2 | | 11 | 2 | | 22 | 2 | +--++ but, I only want to know which traders have platformID 2 *and* platformiID4. In this case, only traderID 5 has both. I can't seem to work out how to structure the statement so it works as a *AND*, not *OR* as above. Any help would be greatly appreciated. Cheers. - Lee. -- View this message in context: http://www.nabble.com/struggling-with-select-query-tf3300643.html#a9181415 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: struggling with select query
You need to embed the PlatformMap and Platforms tables twice Use PM2 as the PlateformMap where PlatformID=2 Use PF2 for PlatformID check of 2 Use PM4 as the PlateformMap where PlatformID=4 Use PF4 for PlatformID check of 4 Join PM2 and PM4 where TraderIDs are equal select distinct TraderPersonalInfo.TraderID,PM2.PlatformID,PM4.PlatformID from TraderPersonalInfo,Locations,Platforms PF2,Platforms PF4, PlatformMap PM2,PlatformMap PM4 where (TraderPersonalInfo.TraderID = PM2.TraderID) and (PM2.PlatformID = PF2.PlatformID) and PM2.PlatformID = 2 and (TraderPersonalInfo.TraderID = PM4.TraderID) and (PM4.PlatformID = PF4.PlatformID) and PM4.PlatformID = 4 and PM2.TraderID=Pm4.TraderID; Give it a try !!! - Original Message - From: lee_m4c [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 27, 2007 7:55:20 AM (GMT-0500) Auto-Detected Subject: struggling with select query Guys, Below is a select query which I'm stuggling with, so I'd be grateful for any help you could give me. select distinct TraderPersonalInfo.TraderID,PlatformMap.PlatformID from TraderPersonalInfo,Locations,PlatformMap,Platforms where (TraderPersonalInfo.TraderID = PlatformMap.TraderID) and (PlatformMap.PlatformID = Platforms.PlatformID) and PlatformMap.PlatformID = 2 or PlatformMap.PlatformID = 4; The query above produces the following output: +--++ | TraderID | PlatformID | +--++ |5 | 4 | |4 | 4 | |1 | 4 | |2 | 4 | |3 | 4 | |6 | 4 | |7 | 4 | |9 | 4 | | 10 | 4 | | 11 | 4 | | 20 | 4 | | 13 | 4 | | 14 | 4 | | 15 | 4 | | 19 | 4 | | 17 | 4 | | 18 | 4 | | 23 | 4 | | 22 | 4 | | 24 | 4 | |2 | 2 | |5 | 2 | |6 | 2 | |7 | 2 | |9 | 2 | | 10 | 2 | | 11 | 2 | | 22 | 2 | +--++ but, I only want to know which traders have platformID 2 *and* platformiID4. In this case, only traderID 5 has both. I can't seem to work out how to structure the statement so it works as a *AND*, not *OR* as above. Any help would be greatly appreciated. Cheers. - Lee. -- View this message in context: http://www.nabble.com/struggling-with-select-query-tf3300643.html#a9181415 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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: struggling with select query
select distinct TraderPersonalInfo.TraderID,PM2.PlatformID,PM4.PlatformID from TraderPersonalInfo,Locations,Platforms PF2,Platforms PF4, PlatformMap PM2,PlatformMap PM4 where (TraderPersonalInfo.TraderID = PM2.TraderID) and (PM2.PlatformID = PF2.PlatformID) and PM2.PlatformID = 2 and (TraderPersonalInfo.TraderID = PM4.TraderID) and (PM4.PlatformID = PF4.PlatformID) and PM4.PlatformID = 4 and PM2.TraderID=Pm4.TraderID; Give it a try !!! select distinct TraderPersonalInfo.TraderID,PlatformMap.PlatformID from TraderPersonalInfo,Locations,PlatformMap,Platforms where (TraderPersonalInfo.TraderID = PlatformMap.TraderID) and (PlatformMap.PlatformID = Platforms.PlatformID) and PlatformMap.PlatformID = 2 or PlatformMap.PlatformID = 4; Instead of wrapping it twice why not just use () around the or statement. I do have a question on this. Isn't using the JOIN statement faster than using a WHERE CLAUSE to join table data? This would allow him to use a simple OR statement at the end. You also have locations in there with no reference what so ever. This would cause redundant work for the SQL engine as it will be seen as a large results set (being result set * number of records in location) prior to being parsed by DISTINCT. select distinct TraderPersonalInfo.TraderID,PlatformMap.PlatformID from TraderPersonalInfo,Locations,PlatformMap,Platforms where (TraderPersonalInfo.TraderID = PlatformMap.TraderID) and (PlatformMap.PlatformID = Platforms.PlatformID) and ( PlatformMap.PlatformID = 2 or PlatformMap.PlatformID = 4 ) ; And why not: SELECT DISTINCT TraderPersonalInfo.TraderID, PlatformMap.PlatformID FROM TraderPersonalInfo,Locations INNER JOIN PlatformMap ON TraderPersonalInfo.TraderID = PlatformMap.TraderID INNER JOIN Platforms ON PlatformMap.PlatformID = Platforms.PlatformID WHERE PlatformMap.PlatformID = 2 or PlatformMap.PlatformID = 4
need a select query
Hi All, I need a select query, with which i can reach to a particular row directly. I mean if a table have 100 rows inserted, we can use select * from table1 limit 10; with this query i will have 10 rows, but my requirement is only 10th row only should come as a result. If u have any solution for this please share with me. Thank you. regards, Bala Raju M.
Re: need a select query
select * from table1 order by field1 limit 10,1 Dusan balaraju mandala napsal(a): Hi All, I need a select query, with which i can reach to a particular row directly. I mean if a table have 100 rows inserted, we can use select * from table1 limit 10; with this query i will have 10 rows, but my requirement is only 10th row only should come as a result. If u have any solution for this please share with me. Thank you. regards, Bala Raju M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a select query
Thank you Guys, your answer helpful to me.
Re: Select query problem
Barry wrote: Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? IF!!! you need IF!! :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select Query taking time
Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. In such a case should this query be considered as slow? We are actually checking for queries which take longer than 12 secs and regarding such queries as slow. The moment we find such a query, a mail is sent to the DBA saying that the query is slow. So in a day there are more than 400 such slow query notifications flowing into the mail box. My questions are, Should the simultaneous queries take so long? Should'nt both queries have finished by 6 secs rather than 10 secs? Is this a right strategy to track slow queries? Any suggestions would help. Thanks, Ratheesh K J
Re: Select Query taking time
On Monday 24 July 2006 09:05, Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. Consider this: With the query cache enabled, running a query for the first time will take 5 seconds. Running it again immediately should be instantaneous, as the result set is in memory (and if it isn't, it should be in the OS disk cache [assuming a small result set]). Two queries executed simultaneously will cause disk contention, because the query isn't cached by mysql, and the OS cache probably hasn't had time to commit the data coming from the disks either. In the case of a single disk serving up the data, two simultaneous queries for the same data will cause the disk to go back and forth trying to satisfy each query. Even with a mirrored pair of disks, you're going to have problems unless you have a very intelligent disk controller that can split the requests across the two disks. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query taking time
On Monday 24 July 2006 09:06, Duncan Hill wrote: On Monday 24 July 2006 09:05, Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using In the case of a single disk serving up the data, two simultaneous queries for the same data will cause the disk to go back and forth trying to satisfy each query. Even with a mirrored pair of disks, you're going to have problems unless you have a very intelligent disk controller that can split the requests across the two disks. Forgot to add - do the queries require table locks? If so, the first one is going to lock the table, run in 5 seconds, unlock. Then the second one, and then the third. Assuming no query cache. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query taking time
Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. In such a case should this query be considered as slow? We are actually checking for queries which take longer than 12 secs and regarding such queries as slow. The moment we find such a query, a mail is sent to the DBA saying that the query is slow. So in a day there are more than 400 such slow query notifications flowing into the mail box. My questions are, Should the simultaneous queries take so long? Should'nt both queries have finished by 6 secs rather than 10 secs? Without query caching enabled, yes it is perfectly normal that the time spent is rising in a linear fashion, eg 4 simulatious would be 20 seconds, 5 25 secs and so on - this just means that your query run by itself is able to utilize all available resources such as cpu time. Look at it this way: 1 query will use 100% of the available cpu and it takes 5 seconds. When you run two at the same time they each have 50% cpu to use, and thus take 10 seconds (5 seconds * 100 / 50). With 3 they each have 33,1/3% and take 15 seconds ( 5 seconds * 100 / 33,1/3) and so on. Is this a right strategy to track slow queries? Yes and no. It is always wise to test your queries to see how the do speed wise, but if you only measure time you aren't really getting the full picture. You have to also look at what else the system is doing - if a query is bottlenecked only by available cpu, it will run at very different speeds depending on how busy the system is with other things - try to bzip2 a 500MB file while running the query and see how much time it takes then for instance ;) And as always remember to use explain to see how mysql optimizes your query so you can modify it if needed, especially complicated joins can sometimes be alot faster if you tweak them a bit. Any suggestions would help. Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Invalid syntax with STD() function when more than one field is used in select query
Oops, the version numbers were 4.1.18-nt and 4.1.19-standard. I have tried it now on the latest 4.1.20 version and still have the same problem. Does anyone have any ideas? Is this a bug? Cheers, Bill -Original Message- From: William Bronsema Sent: Thursday, July 20, 2006 10:18 AM To: mysql@lists.mysql.com Subject: Invalid syntax with STD() function when more than one field is used in select query Hello, I am encountering a strange issue when using the STD function. On my local development machine (MYSQL version 4.18-nt) I can run the following basic SELECT query with no problems: SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY When I test this query on my hosted production machine (MYSQL version 4.19-standard) that query results in an invalid syntax error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY LIM' at line 1 The query will work if I remove the UKEY field in the select: SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 Any ideas? Cheers, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invalid syntax with STD() function when more than one field is used in select query
Hello, I am encountering a strange issue when using the STD function. On my local development machine (MYSQL version 4.18-nt) I can run the following basic SELECT query with no problems: SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY When I test this query on my hosted production machine (MYSQL version 4.19-standard) that query results in an invalid syntax error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY LIM' at line 1 The query will work if I remove the UKEY field in the select: SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 Any ideas? Cheers, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query problem
Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select query problem
Hi I have one problem that i can`t resolve. I have 3 tables TABLE `predmet` ( `PredmetID` int(10) unsigned NOT NULL auto_increment, `BrojPredmeta` int(10) unsigned NOT NULL default '0', `VrstaPredmetaID` int(10) unsigned NOT NULL default '0', `KorisnikID` int(10) unsigned NOT NULL default '0', `GrupaID` int(10) unsigned NOT NULL default '0', `PodgrupaID` int(10) unsigned NOT NULL default '0', `DatumZaduzenja` date NOT NULL default '-00-00', `DatumRazduzenja` date default NULL, `DatumUrgencije` date default NULL, `Komentar` text, TABLE `predmet_referent` ( `PredmetID` int(10) unsigned NOT NULL default '0', `VrstaPredmetaID` int(10) unsigned NOT NULL default '0', `KorisnikID` int(10) unsigned NOT NULL default '0', `GrupaID` int(10) unsigned NOT NULL default '0', `PodgrupaID` int(10) unsigned NOT NULL default '0', `ReferentID` int(10) unsigned NOT NULL default '0', which reference all from table predmet and CREATE TABLE `referent` ( `ReferentID` int(10) unsigned NOT NULL auto_increment, `SifraReferenta` varchar(2) NOT NULL default '', `ImeReferenta` varchar(30) NOT NULL default '', `PrezimeReferenta` varchar(45) NOT NULL default '', PRIMARY KEY (`ReferentID`) My table predmet_referent looks like this PredmetID VrstaPredmetaID KorisnikID GrupaID PodgrupaID ReferentID 1, 1, 18, 4, 4, 1 1, 1, 18, 4, 4, 2 2, 1, 21, 6, 3, 2 2, 1, 21, 6, 3, 3 3, 1, 22, 5, 1, 2 3, 1, 22, 5, 1, 1 3, 1, 22, 5, 1, 3 4, 3, 23, 6, 3, 1 4, 3, 23, 6, 3, 2 4, 3, 23, 6, 3, 3 and table referent looks like ReferentID SifraReferenta ImeReferenta PrezimeReferenta 1, '01', 'Nada', 'Nadi#263;' 2, '03', 'Goran', 'Gavran#269;i#263;' 3, '04', 'Dragan', 'PeriÅ¡iÄ#135;' I want to make select query so thatt result from that query look something like this PredmetID BrojPredmeta Referent1 Referent2 Referent3 1 121215 12 2 121355 23 3 236564 21 3 4 213545 12 3 How can i do this?This is very importat for me Any help will be great. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT Query GROUP BY
Hello MySQL Users I have a contract table. Each contract has a certain level, which can be in a range from 1-5. This information is stored as a number. There is no additional table for the levels. I would like to get a list with the amount of contracts of each level - including 0 for the levels with no contracts. Until now I just used : SELECT COUNT(*), level FROM contract GROUP BY level but this is just showing level with contracts. I tried a right join with a table which contains just integer values. Seems like a workaround, but I'm interested in a easier aolution - I bet there is one. Thank you! Jay PS: I'm using Version 4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Query GROUP BY
The schema of your contract should be like this: Contract (id, level, ...) where column 'id' is the primary key, isn't it? If so, you can try this: SELECT COUNT(id) FROM contract GROUP BY level - Original Message - From: Jay [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 11, 2006 5:41 PM Subject: SELECT Query GROUP BY Hello MySQL Users I have a contract table. Each contract has a certain level, which can be in a range from 1-5. This information is stored as a number. There is no additional table for the levels. I would like to get a list with the amount of contracts of each level - including 0 for the levels with no contracts. Until now I just used : SELECT COUNT(*), level FROM contract GROUP BY level but this is just showing level with contracts. I tried a right join with a table which contains just integer values. Seems like a workaround, but I'm interested in a easier aolution - I bet there is one. Thank you! Jay PS: I'm using Version 4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Query GROUP BY
Thank you, Peng Yi-fan but incase there is no contract with the level 5, it will not be shown. I would like to see: level amount 1 34 2 0 3 18 4 986 5 0 I could add it in the application, but I try to do it within the Query. btw. the right join I mentioned, doesn't work. Has someone another idea? Thank you! Jay The schema of your contract should be like this: Contract (id, level, ...) where column 'id' is the primary key, isn't it? If so, you can try this: SELECT COUNT(id) FROM contract GROUP BY level - Original Message - From: Jay [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 11, 2006 5:41 PM Subject: SELECT Query GROUP BY Hello MySQL Users I have a contract table. Each contract has a certain level, which can be in a range from 1-5. This information is stored as a number. There is no additional table for the levels. I would like to get a list with the amount of contracts of each level - including 0 for the levels with no contracts. Until now I just used : SELECT COUNT(*), level FROM contract GROUP BY level but this is just showing level with contracts. I tried a right join with a table which contains just integer values. Seems like a workaround, but I'm interested in a easier aolution - I bet there is one. Thank you! Jay PS: I'm using Version 4.1 -- 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: SELECT Query GROUP BY
The easiest thing to do would be to create an additional table containing all the possible valid values for contract level, then join on that table to show counts. Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there are no 5's, but not also show the count for every other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.) CREATE TABLE contractlevel ( level int ); then INSERT 1, 2, 3, etc. then you need a LEFT JOIN like so: select cl.level, count(c.level) as count from contractlevel cl left join contract c using (level) group by cl.level; Hope this helps! Dan Jay wrote: Thank you, Peng Yi-fan but incase there is no contract with the level 5, it will not be shown. I would like to see: level amount 1 34 2 0 3 18 4 986 5 0 I could add it in the application, but I try to do it within the Query. btw. the right join I mentioned, doesn't work. Has someone another idea? Thank you! Jay The schema of your contract should be like this: Contract (id, level, ...) where column 'id' is the primary key, isn't it? If so, you can try this: SELECT COUNT(id) FROM contract GROUP BY level - Original Message - From: Jay [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 11, 2006 5:41 PM Subject: SELECT Query GROUP BY Hello MySQL Users I have a contract table. Each contract has a certain level, which can be in a range from 1-5. This information is stored as a number. There is no additional table for the levels. I would like to get a list with the amount of contracts of each level - including 0 for the levels with no contracts. Until now I just used : SELECT COUNT(*), level FROM contract GROUP BY level but this is just showing level with contracts. I tried a right join with a table which contains just integer values. Seems like a workaround, but I'm interested in a easier aolution - I bet there is one. Thank you! Jay PS: I'm using Version 4.1 -- 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: SELECT Query GROUP BY
Thank you Dan, [...] Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there are no 5's, but not also show the count for every other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.) [...] Sure, easy to understand. I was thinking in a (1,2,3,4,5) list instead of a table Thank you very much! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Query GROUP BY
No problem, glad to help. I noticed your comment in an earlier message about it seeming like a workaround - I don't think it seems like a workaround at all. Having a table with the possible values makes for a normal database structure, and an approach that should keep you from having to modify your application's SQL queries when someone decides to add contract levels 6 through 10 and then later 11 and 12, for example. I also wouldn't be concerned about performance using such a join - SQL database servers are optimized for JOIN operations. They do them very well. In your case I'd add a UNIQUE index on the contractlevel table, more to guard against duplicate values than for performance, though it certainly won't hurt performance. Dan Jay wrote: Thank you Dan, [...] Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there are no 5's, but not also show the count for every other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.) [...] Sure, easy to understand. I was thinking in a (1,2,3,4,5) list instead of a table Thank you very much! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL select query - newbie
Hi! System is Novell SBS 6.5 sp1 with Apache 2.0.48, MySQL ver. 4.0.15a, PHP 4.2.3. We try to define a select query that takes the values of the variables 'varKoshi' and 'varKvish' based on the following 4 tables: 'varKoshi' vv 'varKvish' v v Table1: access_diff v v Table4: tracks -- v v --- access_diff.DiffLevel tracks.Kvish_Access access_diff.Access_ID v tracks.Track_ID vv vTable2: accesses v v v accesses.Access_IDv accesses.Track_ID v accesses.Site_ID v v Table3: sites v - sites.Site_ID sites.Site_Short_Description RESULTS We want to find all records that: A. 1. 'varKoshi' = access_diff.DiffLevel (Table1) 2. access_diff.Access_ID (Table1) = access_diff.Access_ID (Table2) B. 1. 'varKvish' = tracks.Kvish_Access (Table4) 2. tracks.Track_ID (Table4) = accesses.Track_ID (Table2) C. The common values for accesses.Site_ID (Table2) from A and B are equal to the values in sites.Site_ID (Table3) to retrieve the corresponding records in the sites.Site_Short_Description Table3) as the RESULTS. The query we did is: SELECT sites.Site_Short_Description FROM sites, accesses, access_diff, tracks WHERE access_diff.DiffLevel = 'varKoshi' and accesses.Access_ID = access_diff.Access_ID and tracks.Kvish_Access = 'varKvish' and accesses.Track_ID = tracks.Track_ID and sites.Site_ID = accesses.Site_ID The problem is that not every time all the correct records (possibilities) are found. TIA Nanu
Re: MySQL select query - newbie
Nanu Kalmanovitz wrote: Hi! System is Novell SBS 6.5 sp1 with Apache 2.0.48, MySQL ver. 4.0.15a, PHP 4.2.3. We try to define a select query that takes the values of the variables 'varKoshi' and 'varKvish' based on the following 4 tables: 'varKoshi' vv 'varKvish' v v Table1: access_diff v v Table4: tracks -- v v --- access_diff.DiffLevel tracks.Kvish_Access access_diff.Access_ID v tracks.Track_ID vv vTable2: accesses v v v accesses.Access_IDv accesses.Track_ID v accesses.Site_ID v v Table3: sites v - sites.Site_ID sites.Site_Short_Description RESULTS Your graphic is garbled. Did you perhaps not use a fixed width type font to compose it? We want to find all records that: A. 1. 'varKoshi' = access_diff.DiffLevel (Table1) 2. access_diff.Access_ID (Table1) = access_diff.Access_ID (Table2) B. 1. 'varKvish' = tracks.Kvish_Access (Table4) 2. tracks.Track_ID (Table4) = accesses.Track_ID (Table2) C. The common values for accesses.Site_ID (Table2) from A and B are equal to the values in sites.Site_ID (Table3) to retrieve the corresponding records in the sites.Site_Short_Description Table3) as the RESULTS. The query we did is: SELECT sites.Site_Short_Description FROM sites, accesses, access_diff, tracks WHERE access_diff.DiffLevel = 'varKoshi' and accesses.Access_ID = access_diff.Access_ID and tracks.Kvish_Access = 'varKvish' and accesses.Track_ID = tracks.Track_ID and sites.Site_ID = accesses.Site_ID The problem is that not every time all the correct records (possibilities) are found. Your query looks good to me. The problem must be bad data. --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A doubt in SELECT query
hello all, In which order the datas are displayed, when the SELECT quey is used ? Is it random or the order in which the datas are inserted? -- r.subramani My log file: http://subramanitce.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A doubt in SELECT query
subramani wrote: hello all, In which order the datas are displayed, when the SELECT quey is used ? Is it random or the order in which the datas are inserted? -- r.subramani My log file: http://subramanitce.blogspot.com Random -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A doubt in SELECT query
If the sequence in which the result set is displayed is important to you, you will need to use SQL clauses to force the sequence you want. ORDER BY is the main way of accomplishing this although other clauses, like GROUP BY and DISTINCT, can also affect the sequence. But ORDER BY is the normal method of forcing the output to be in a specific order. -- Rhino - Original Message - From: subramani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 07, 2006 10:22 AM Subject: A doubt in SELECT query hello all, In which order the datas are displayed, when the SELECT quey is used ? Is it random or the order in which the datas are inserted? -- r.subramani My log file: http://subramanitce.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.5/303 - Release Date: 06/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.5/303 - Release Date: 06/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated select query
Gabriel PREDA wrote: About the first problem I think you need to give us more data ! Okay i thought yesterday how i can make up some real problem and have this: Guess you have a database with customers and a database with projects. Those prijects have timestamps so you know what time he added that project. Now my problem is to get the First project the customer has added. And i want it to have it like ++--+---+ |Customer|Time |Projectname| ++--+---+ |John|2005-11-23|Teatime| |Jim |2005-02-19|Having Lunch | |Bob |2005-06-11|My big Project!| ++--+---+ So that i have the first project every customer has added first and only that. But the Databases looks like Customer: +--++--++---+-+ |id|Fnam|Snam |Street |Code |City | +--++--++---+-+ |01|John|Doh |Funnystreet 4 |87624 |Somewherecity| |02|Jim |Bobjoe|Anotherfunnystreet 8|213+E13|Othercity| |03|Bob |Joejim|boringstreet 67 |324456 |Boringcity | +--++--++---+-+ Projects +--+-++--+ |id|parent_id|Name|Time | +--+-++--+ |01|01 |Teatime |2005-11-23| |02|01 |Suppertime |2005-12-14| |03|02 |having Lunch|2005-02-19| |04|02 |having Dinner |2005-04-12| |05|02 |having something|2005-07-17| |06|03 |My small Project|2005-02-10| |07|03 |My big Project! |2005-06-11| +--+-++--+ I hope this enlights it a bit Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated select query
|06|03 |My small Project|2005-02-10| |07|03 |My big Project! |2005-06-11| Small mistake. The project with ID 06 should have a date above ID 07. Sorry for that! -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated select query
try something like this select customer,max(time),name from customers join projects on projects.parent_id=customer.id group by customer; regards Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services Barry [EMAIL PROTECTED] wrote: Gabriel PREDA wrote: About the first problem I think you need to give us more data ! Okay i thought yesterday how i can make up some real problem and have this: Guess you have a database with customers and a database with projects. Those prijects have timestamps so you know what time he added that project. Now my problem is to get the First project the customer has added. And i want it to have it like ++--+---+ |Customer|Time |Projectname| ++--+---+ |John|2005-11-23|Teatime| |Jim |2005-02-19|Having Lunch | |Bob |2005-06-11|My big Project!| ++--+---+ So that i have the first project every customer has added first and only that. But the Databases looks like Customer: +--++--++---+-+ |id|Fnam|Snam |Street |Code |City | +--++--++---+-+ |01|John|Doh |Funnystreet 4 |87624 |Somewherecity| |02|Jim |Bobjoe|Anotherfunnystreet 8|213+E13|Othercity| |03|Bob |Joejim|boringstreet 67 |324456 |Boringcity | +--++--++---+-+ Projects +--+-++--+ |id|parent_id|Name|Time | +--+-++--+ |01|01 |Teatime |2005-11-23| |02|01 |Suppertime |2005-12-14| |03|02 |having Lunch|2005-02-19| |04|02 |having Dinner |2005-04-12| |05|02 |having something|2005-07-17| |06|03 |My small Project|2005-02-10| |07|03 |My big Project! |2005-06-11| +--+-++--+ I hope this enlights it a bit Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complicated select query
Hello Everyone! Probably somone can help me out. I have 2 databases and it looks like: db1 Project 1 db2 Subproject 1 db2 Subproject 2 db1 Project 2 db2 Subproject 1 db1 Project 3 db2 Subproject 1 db2 Subproject 2 db2 Subproject 3 What kind of a query would give me the first Subproject out of dv2 of each Project in db1? And er is it possible to have loops in mysql? Like SELECT * FROM db WHERE id = 5 STEP id +5 UNTIL id = 50 +--+---+ |id|val| +--+---+ |5 |10 | |10|xy | |15|jks| ... ... |50|763| +--+---+ and so on. Thanks for any Help! Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated select query
About the first problem I think you need to give us more data ! As for the seccond... I haven't sen such loops yet... But you can go arround them and do something like: SELECT * FROM db WHERE (id BETWEEN 5 AND 50) AND id%5=0 -- Gabriel PREDA Senior Web Developer
Re: Complicated select query
Barry, Hello Everyone! Probably somone can help me out. I have 2 databases and it looks like: (Wayne Ratliff is dead but his mistake lives on :-) ). You mean tables, right? db1 Project 1 db2 Subproject 1 db2 Subproject 2 db1 Project 2 db2 Subproject 1 db1 Project 3 db2 Subproject 1 db2 Subproject 2 db2 Subproject 3 What kind of a query would give me the first Subproject out of dv2 of each Project in db1? A hierarchical query, ie a query which traverses the data as a graph. They're doable in MySQL, eg see http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. A simple nodes-and-edges (Edge List) model should do fine for your problem. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.1/292 - Release Date: 3/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT...SELECT Query Help Request.
Dear All, First of all, I would like to thank Shawn Green, Peter Brawley and Josh for their kind help in my previous issue. I have a table named master_list with two field customer_id and list_code. I need to insert only non existent records in master_list from a child_list. Both master_list and child_list table structure are identical but data in child_list may contain records from master_list and new records. I have constructed query using INSERT SELECT but I am unable to check and filter out records that already exist in master_list. INSERT INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list Is there anyway to check and insert records in master_list without creating dups? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Re: INSERT...SELECT Query Help Request.
Yesmin Patwary [EMAIL PROTECTED] wrote on 03/22/2006 09:56:20 AM: Dear All, First of all, I would like to thank Shawn Green, Peter Brawley and Josh for their kind help in my previous issue. I have a table named master_list with two field customer_id and list_code. I need to insert only non existent records in master_list from a child_list. Both master_list and child_list table structure are identical but data in child_list may contain records from master_list and new records. I have constructed query using INSERT…SELECT but I am unable to check and filter out records that already exist in master_list. INSERT INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list Is there anyway to check and insert records in master_list without creating dups? Thanks in advance for any help. If you have a unique key or primary key set up on (master_list.customer_id, master_list.list_code) it's more simple that you think. Just add the word IGNORE to your INSERT statement like this :-) INSERT IGNORE INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list usage details are here: http://dev.mysql.com/doc/refman/5.0/en/insert.html The IGNORE will tell the engine to disregard all duplicate key errors and continue processing rows. If you don't have such a key, I suggest you add one or let us know why you can't create it. Which workaround we can use for the lack of the key will depend on the version you are using. You are most welcome! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help with a SELECT query
Hello.
Usually working with IP addresses in a numeric form is faster. Use
INET_NTOA() and INET_ATON() functions to store IP addresses as unsigned
ints. To work with subnetworks instead of like 'xxx.xxx.%' use
ip_address_in_numeric_form between inet_aton('xxx.xxx.0.0') and
inet_aton('xxx.xxx.255.255') or similar condition (check if between
covers the borders of the subnet). %php% are usually slow. Force your
application which inserts data to the database explicitly determine
the type of the content (perhaps, you should add a field which will
indicate the content. I agree, that this is a superfluity, however
the speed of the query is more important).
Jay Paulson (CE CEN) [EMAIL PROTECTED] wrote:
Below is a query I'm trying to create and the sql for the table I'm pulling the
information out of. The query is definitely not the best query out there
especially
since I'm still pretty new with sql. I know there has to be a better way of
getting the
information I want. A little background. I'm parsing an Apache access_log
file and
throwing it all into a DB so I can run some reports on it. What I'm wanting
to get are
certain file types that were downloaded (in this case all .html, .php, .pdf,
.doc, and
.flv files) in a certain date range but grouped by certain ip addresses. Some
groups of
ips might have 2 or 3 subnets it needs to get (ie xxx.xxx.% and yyy.yyy.%).
What needs
to be returned is the count of all the file types that have been downloaded
but grouped
by certain ips that I'm looking for.
I hope I didn't confuse anything because I think I confused myself! ;)
Thanks for any help!
CREATE TABLE `apache_statslog` (
`STATS_ID` int(11) NOT NULL auto_increment,
`ip` varchar(25) default NULL,
`accesstime` datetime default NULL,
`thepage` varchar(250) default NULL,
`thetype` varchar(25) default NULL,
`thecode` char(3) default NULL,
`thebytes` int(11) default NULL,
`theref` varchar(250) default NULL,
`browser` varchar(250) default NULL,
PRIMARY KEY (`STATS_ID`),
KEY `ip` (`ip`),
KEY `accesstime` (`accesstime`),
KEY `thepage` (`thepage`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59145 ;
SELECT count(swrm.ip) as swrm_page_hits, count(mw.ip) as mw_page_hits,
count(fl.ip) as
fl_page_hits, count(so.ip) as so_page_hits
FROM apache_statslog as swrm, apache_statslog as mw, apache_statslog as fl,
apache_statslog as so
WHERE (swrm.accesstime = '2006-01-01 00:00:00' AND swrm.accesstime =
'2006-01-04
23:59:59')
AND (swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip
LIKE 'xxx.xxx.%')
#this is a group that needs to return a count
AND (mw.ip LIKE 'xxx.xxx.%' OR mw.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (fl.ip LIKE 'xxx.xxx.%' OR fl.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (so.ip LIKE 'xxx.xxx.%' OR so.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (swrm.thepage LIKE '%.html%' OR swrm.thepage LIKE '%.php%' OR
swrm.thepage LIKE
'%.doc%' OR swrm.thepage LIKE '%.pdf%' OR swrm.thepage LIKE '%.flv%')
ORDER BY swrm.accesstime ASC
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
