Re: what's better query?
The two querys's logic are different.Which is you need? - Original Message - From: afan pasalic [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 04, 2008 5:50 AM Subject: what's better query? Hi, I have two tables, people and membership. Both have email column (different column names though). CREATE TABLE `people` ( `person_id` int(8) unsigned NOT NULL, `address_id` int(8) default NULL, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `email` varchar(50) default NULL, PRIMARY KEY (`person_id`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `membership` ( `person_id` int(8) unsigned NOT NULL, `organization_id` int(8) unsigned NOT NULL, `email_address` varchar(45) default NULL, `title` varchar(45) default NULL, `department` varchar(45) default NULL, `direct_phone` varchar(10) default NULL, PRIMARY KEY (`person_id`,`organization_id`), KEY `index_email` (`email_address`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I need to check if entered email address exists in either one of these two tables. I made these two queries. Which one is better one? ( SELECT p.person_id, p.email, p.first_name, p.last_name FROM people p WHERE email='.$email.' ) UNION ( SELECT m.person_id, m.email_address as email, p.first_name, p.last_name FROM membership m LEFT JOIN people p ON p.person_id=m.person_id WHERE m.email_address='.$email.' ) or: SELECT p.person_id, p.email, p.first_name, p.last_name, m.email_address FROM people p, membership m WHERE (p.email='.$email.' OR m.email_address='.$email.') AND p.person_id=m.person_id Thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error while starting replication for the first time
Replication is a complicated process.I can't see what you do and what happened for your computer. From you description,i think you maybe save some old file in your entironment and some configer parmater point to it. So,the best way is sending you actions to me step by step and we will analyse what problem happened. The simple way is re-install your salve and recover mysql data and rebuild Replication environment. - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: Moon's Father [EMAIL PROTECTED] Cc: Mike [EMAIL PROTECTED]; Krishna Chandra Prajapati [EMAIL PROTECTED]; MYSQL General List mysql@lists.mysql.com Sent: Monday, May 26, 2008 1:03 PM Subject: Re: error while starting replication for the first time Hi All, The issue has been solved, we removed relay-log.index files which was refering to the old relay-log file. After this the CHANGE MASTER worked and replication is going on fine. regards anandkl On 5/24/08, Moon's Father [EMAIL PROTECTED] wrote: I think you should check the error with perror at first. On Sat, May 24, 2008 at 12:46 AM, Mike [EMAIL PROTECTED] wrote: On Fri, May 23, 2008 at 12:08 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Mike, Since i have done a fresh backup from master (with master db down), and copied over the same to slave. Then why is the slaving looking for old relay-log. I also did the RESET SLAVE, bit still getting error. I am wondering, why is the slave looking for old relay-logs Your my.cnf might have relaylog info in it. You might have a master.info or relay log files in your mysql directory. The slave does need relay logs to replicate. So if you keep use RESET MASTER after you backup the mysql then you backup is worthless. Tell me the steps you are using? On 5/23/08, Mike [EMAIL PROTECTED] wrote: Ok, since you do not have the bin log you need to start over again with the replication and do either a mysqldump or get the rawdata. then once you have the data then you can start replication again. http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-backups-mysqldump.html http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-backups-rawdata.html On Fri, May 23, 2008 at 10:37 AM, Ananda Kumar [EMAIL PROTECTED] wrote: RESET MASTER...delete all the bin logs. Infact i tried this on the slave as it also a MASTER to itself, but the error kept on coming. regards anandkl On 5/23/08, Mike [EMAIL PROTECTED] wrote: On Fri, May 23, 2008 at 8:13 AM, Ananda Kumar [EMAIL PROTECTED] wrote: Files is owned by mysql, but the point is, these relay-log files are not present. Before setting up the slave, i cleaned up all files. If you want the logs back you could use RESET MASTER maybe. http://dev.mysql.com/doc/refman/5.0/en/reset-master.html Let me know. :-) regards anandkl On 5/23/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Please check the ownership of the files copied on the slave. Give permissions by chown -R mysql:mysql On Fri, May 23, 2008 at 2:47 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Krishna, As i said, this is a complete copy from master db to slave. I have deleted all old files from slave and setting up from scratch, by taking a complete backup from master, copying over to slave and using the change master command with BIN LOG and POSITION taken before taking a backup copy from master. On 5/23/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: check permissions On Fri, May 23, 2008 at 12:33 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I shutdown my master, took a db files backup. Copied it over to slave machine. I executed change master command, then when i start slave slave start, i get the below error. I did reset slave, but still getting same error, what could be the reason, and how to fix it. 080522 23:04:05 [ERROR] Failed to open log (file '/data/mysql-log/relay-log/relay.000791', errno 2) 080522 23:04:05 [ERROR] Failed to open the relay log 'FIRST' (relay_log_pos 4) I am using mysql 5.0.41 community version, on debain. regards anandkl -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here:
Re: CONCAT doesn't work with NULL?
i execute follow sql.
select concat_ws('','d','\n','c');
the result is :
++
| concat_ws('','d','\n','c') |
++
| d
c|
++
There are no result as you said.
- Original Message -
From: Afan Pasalic [EMAIL PROTECTED]
To: ewen fortune [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, May 15, 2008 12:52 AM
Subject: Re: CONCAT doesn't work with NULL?
actually, this will not work for me (or I got it wrong :D) because I
need to have street, state and zip in one line and with separator
defined on the beginning it will put everything in separate lines.
:D
ewen fortune wrote:
Hi Afan,
You can use concat_ws
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
---
CONCAT() returns NULL if any argument is NULL.
CONCAT_WS() does not skip empty strings. However, it does skip any
NULL values after the separator argument
---
Ewen
On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote:
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n',
r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n',
r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
-afan
--
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]
difference between max_connection and max_user_connection
Are there any difference between max_connection and max_user_connection? thanks! _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I can't find any variables used show variables
My kernel version is 5.0.12. When i learn the manual,i didn't find any variables in the manul when i used show variables.Weither there is a lack of manual updating? thanks _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
the difference between server option and Server System Variables
In mysql,server system variables's function like server option function. How do i choosing between the server opiton and the system variables. thanks! _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: the difference between server option and Server System Variables
I want to know when i need using the server option and when i need the server system variables.In other words,can i using server system variables replace all server options. thanks. From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: 王 旭 [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: the difference between server option and Server System Variables Date: Tue, 18 Oct 2005 21:20:32 -0500 liulian, In mysql,server system variables's function like server option function. How do i choosing between the server opiton and the system variables. thanks! MySQL has more than 300 system variables that can be set in the option file my.cnf/ini, on the server command line, and/or with SELECT statements. Some can be set with all those methods, some with just one or two of them. What do you want to know? PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can't find any variables used show variables
yes. I look reference manual of mysql5.0.12. For example,there a variables named bdb_..,but i can't find these variables used show variables. From: Paul DuBois [EMAIL PROTECTED] To: 猝 嗥 [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: I can't find any variables used show variables Date: Tue, 18 Oct 2005 21:20:20 -0500 At 10:11 +0800 10/19/05, 猝 嗥 wrote: My kernel version is 5.0.12. When i learn the manual,i didn't find any variables in the manul when i used show variables.Weither there is a lack of manual updating? Did you look here? http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a question about system page.
I learn configuring the mysql server in the manul 5.3.1.I notice there is a parameter --large pages.Please tell my how do i alter linux system memory pages . _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About database criterion
thanks gleb! I know tpc constitute a performance standard for database.For example,tpc-c,tpc-w. Now I want to know whether there are a standard for database system function.For example,maybe some describe in the standard : a good database must have view manage,index manage,trigger manage etc. From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: About database criterion Date: Wed, 14 Sep 2005 14:31:53 +0300 Hello. For a pity, your question isn't clear enough. Please, could describe the issue more widely. $ $ [EMAIL PROTECTED] wrote: Hello,every one. Is there a current criterion about database function to evaluating a database? _ MSN Explorer: http://explorer.msn.com/lccn/ -- 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] _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About database criterion
Hello,every one. Is there a current criterion about database function to evaluating a database? _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a question in SERIALIZABLE transaction isolation level
In the transaction isolation level,Database send some duplicate entry message to me. Why? _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize a sql statement
sorry,my english is poor. Follow is my mean. One,I create a index.(create index orders_test on orders(o_id,o_c_id)) Two,I do explain again. Follow is result -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'orders_test', '4', '', 517890, 'Using where; Using index; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' -- Is the performance of sql statement be increased? From: Sebastian [EMAIL PROTECTED] To: 王 旭 [EMAIL PROTECTED] CC: [EMAIL PROTECTED], mysql@lists.mysql.com, [EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Fri, 22 Jul 2005 01:47:24 -0400 wow consider a different font when posting.. gives me a headache reading that. 王 旭 wrote: I analyzed the query plan again. --- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 348660, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' - In the plan,i find there are 348660 row scan in table orders.And in the sql statement,orders.o_id is be used to join operation and orders.o_c_id be used in the where statement.So i create a index orders_test on table orders(o_id and o_c_id). After create the index.I execute the explain again.Follow is the result: --- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY', '4', '', 348660, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' I find the index order_test be set in the possible_keys but not set in the keys. So i alter the sql statement with use index statement. Follow is the explain output after alter: 1, 'PRIMARY', 'orders', 'range', 'orders_test', 'orders_test', '4', '', 519210, 'Using where; Using index; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' - Performance maybe much more bad. Now i have two question. One,Are there any probability for optimization performance using this way? Two,I am not able to calculate the cost time with the explain output.I have learned the knowlege about 7.2.2. Estimating Query Performance in help.But i can't understand. thanks From: 王 旭 [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com, [EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Thu, 21 Jul 2005 18:46:32 +0800 Thank you SGreen.But i can'optimize the sql statement like your way
Re: optimize a sql statement
Thank you SGreen.But i can'optimize the sql statement like your way because this query must describe in one sql statement.So i think i maybe optimize this sql statement through creating high efficiency index or describe this sql statement in other method. Rewrite this sql statement must in one sql statement. Follow is the original sql statement: - SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and o_id0 AND not(order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY SUM(ol_qty)DESC limit 5 - Follow is the original explain output: -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 519414, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' --- In the mail,Pow give me a way.He advise me to build a index on orders.o_id and orders.o_c_id.I understanding his mean is table orders join table order_line using the index.Because orders.o_c_id be limited in the where statemetn,so the index should be filter date before Join operation.In this way,the index of orders using much less data to join the table order_line.But i can't see the effect when i do like it.I don't know the reason. Are you have any better way? From: [EMAIL PROTECTED] To: 王 旭 [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Tue, 19 Jul 2005 16:10:18 -0400 王 旭 [EMAIL PROTECTED] wrote on 07/18/2005 03:12:28 AM: NO effect :-( From: pow [EMAIL PROTECTED] To: 王 旭 [EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Mon, 18 Jul 2005 11:51:23 +0800 Do u have composite index on order_line.ol_o_id AND order_line.ol_i_id? You could try that... 王 旭 wrote: Now,I make this sql statement to easy. Follow is the sql statement: - SELECT ol_i_id FROM orders,order_line WHERE order_line.ol_o_id = orders.o_id GROUP BY ol_i_id - Follow is the explain output: - 1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 259231, 'Using index; Using temporary; Using filesort' 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'PRIMARY', '4', 'tpcw.orders.o_id', 1, '' - Can it be optimized? From: 王 旭 [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: optimize a sql statement Date: Sat, 16 Jul 2005 18:24:15 +0800 Follow is my sql statement: - SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 -- follows are explain output: -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 19398, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1
Re: optimize a sql statement
I analyzed the query plan again. --- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 348660, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' - In the plan,i find there are 348660 row scan in table orders.And in the sql statement,orders.o_id is be used to join operation and orders.o_c_id be used in the where statement.So i create a index orders_test on table orders(o_id and o_c_id). After create the index.I execute the explain again.Follow is the result: --- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY', '4', '', 348660, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' I find the index order_test be set in the possible_keys but not set in the keys. So i alter the sql statement with use index statement. Follow is the explain output after alter: 1, 'PRIMARY', 'orders', 'range', 'orders_test', 'orders_test', '4', '', 519210, 'Using where; Using index; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' - Performance maybe much more bad. Now i have two question. One,Are there any probability for optimization performance using this way? Two,I am not able to calculate the cost time with the explain output.I have learned the knowlege about 7.2.2. Estimating Query Performance in help.But i can't understand. thanks From: 王 旭 [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com, [EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Thu, 21 Jul 2005 18:46:32 +0800 Thank you SGreen.But i can'optimize the sql statement like your way because this query must describe in one sql statement.So i think i maybe optimize this sql statement through creating high efficiency index or describe this sql statement in other method. Rewrite this sql statement must in one sql statement. Follow is the original sql statement: - SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and o_id0 AND not(order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY SUM(ol_qty)DESC limit 5 - Follow is the original explain output: -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 519414, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id
about: Select tables optimized away
When I explain SELECT MAX(o_id)-1 FROM orders,it tell me Select tables optimized away.What is it meaning?It show me good or bad? thanks! _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize a sql statement
NO effect :-( From: pow [EMAIL PROTECTED] To: 王 旭 [EMAIL PROTECTED] Subject: Re: optimize a sql statement Date: Mon, 18 Jul 2005 11:51:23 +0800 Do u have composite index on order_line.ol_o_id AND order_line.ol_i_id? You could try that... 王 旭 wrote: Now,I make this sql statement to easy. Follow is the sql statement: - SELECT ol_i_id FROM orders,order_line WHERE order_line.ol_o_id = orders.o_id GROUP BY ol_i_id - Follow is the explain output: - 1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 259231, 'Using index; Using temporary; Using filesort' 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'PRIMARY', '4', 'tpcw.orders.o_id', 1, '' - Can it be optimized? From: 王 旭 [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: optimize a sql statement Date: Sat, 16 Jul 2005 18:24:15 +0800 Follow is my sql statement: - SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 -- follows are explain output: -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 19398, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' 2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' -- This sql statement performance is too bad.Please help me to optimize it . thanks! _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize a order by statement
Follow is the sql statement: explain SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and o_id0 ORDER BY SUM(ol_qty)DESC Follow is the explain output: 1, 'SIMPLE', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 129615, 'Using where; Using index; Using temporary' 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, '' The query plan use Using temporary.Can i optimize this sql statement? _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: optimize a sql statement
Now,I make this sql statement to easy. Follow is the sql statement: - SELECT ol_i_id FROM orders,order_line WHERE order_line.ol_o_id = orders.o_id GROUP BY ol_i_id - Follow is the explain output: - 1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 259231, 'Using index; Using temporary; Using filesort' 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'PRIMARY', '4', 'tpcw.orders.o_id', 1, '' - Can it be optimized? From: 王 旭 [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: optimize a sql statement Date: Sat, 16 Jul 2005 18:24:15 +0800 Follow is my sql statement: - SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 -- follows are explain output: -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 19398, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' 2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' -- This sql statement performance is too bad.Please help me to optimize it . thanks! _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize a sql statement
Follow is my sql statement: - SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 500) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 500) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 Follow is the explain output: --- 1, 'PRIMARY', 'order_line', 'range', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'PRIMARY', '4', '', 13064, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'orders', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'tpcw.order_line.ol_o_id', 1, 'Using where' 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' 2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' Using temporary; Using filesort is bad ways.Could i optimize it via index or rebuild the sql statement? Thanks! _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize a sql statement
Follow is my sql statement: - SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id and orders.o_id (SELECT MAX(o_id)-1 FROM orders) AND order_line.ol_i_id = 5000) GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50 -- follows are explain output: -- 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 19398, 'Using where; Using temporary; Using filesort' 1, 'PRIMARY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id', 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index' 3, 'DEPENDENT SUBQUERY', 'order_line', 'ref', 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where' 4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' 2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away' -- This sql statement performance is too bad.Please help me to optimize it . thanks! _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
