Re: MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
Scott Klarenbach [EMAIL PROTECTED] wrote on 02/02/2006 02:01:11 PM: I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach Yes, and no. You cannot apply a LIMIT specifically to a JOIN clause unless you break your query into separate pieces and put limits on each of them. What happens during the normal execution of a query is that after parsing and planning the engine begins collecting and combining the source data. Which records are combined and matched against which others is defined in the FROM clause and all of the JOIN clauses. The equivalent to a large virtual table (similar to saying SELECT * FROM all involved tables) is created in memory. The only restrictions to which rows of data make it into this first processing stage come from the ON clauses (and any WHERE clauses the optimizer _may_ choose to include) defined between the JOINed tables. Next comes WHERE clause processing, then GROUP BY processing, HAVING processing, ORDER BY processing, and finally LIMIT processing. As you can see by the flow of query execution, LIMIT clauses are really only useful for restricting how much data is finally sent to the user. In order to minimize how much processing your CPU has to do to compute a particular query you have several tools at your disposal: indexes, temporary tables, and stepwize result construction. JOINing tables is a geometrically expensive action. The number of potential row matches increase by the product of the number of rows in each table involved in the join. If you can preselect certain target rows from your really large tables into smaller temporary tables and build your final result set from them, the query processor will only need to compute a small fraction of the row comparisons it would have had to perform compared to the number of row comparisons necessary to JOIN your original tables. Take this rough math as an example: TABLE A: 1 rows TABLE B: 1 rows SELECT * from A INNER JOIN B ON A.id = B.A_ic; There are potentially 1 x 1 = 1 (1.0e+08) row combinations to be checked. If instead of joining A to B, we create two derivative tables called C and D (assuming we don't change the column names) TABLE A - TABLE C: 5000 rows TABLE B - TABLE D: 1000 rows SELECT * from C INNER JOIN D ON C.id = D.A_ic; That means there are now 5000 x 1000 = 500 (5.0e+06) or 1/20th the number of comparisons to run. Computing tables C and D should be in linear or logarithmic time (because you should have good index coverage) so there will usually be a net gain in performance. This is the secret to stepwize result construction. To help you to optimize your particular query, I would need to see it and the table definitions it is working against (SHOW CREATE TABLE works best for me). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
Thanks a lot Shawn. As always, your advice has been very helpful. On 2/3/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 02/02/2006 02:01:11 PM: I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach Yes, and no. You cannot apply a LIMIT specifically to a JOIN clause unless you break your query into separate pieces and put limits on each of them. What happens during the normal execution of a query is that after parsing and planning the engine begins collecting and combining the source data. Which records are combined and matched against which others is defined in the FROM clause and all of the JOIN clauses. The equivalent to a large virtual table (similar to saying SELECT * FROM all involved tables) is created in memory. The only restrictions to which rows of data make it into this first processing stage come from the ON clauses (and any WHERE clauses the optimizer _may_ choose to include) defined between the JOINed tables. Next comes WHERE clause processing, then GROUP BY processing, HAVING processing, ORDER BY processing, and finally LIMIT processing. As you can see by the flow of query execution, LIMIT clauses are really only useful for restricting how much data is finally sent to the user. In order to minimize how much processing your CPU has to do to compute a particular query you have several tools at your disposal: indexes, temporary tables, and stepwize result construction. JOINing tables is a geometrically expensive action. The number of potential row matches increase by the product of the number of rows in each table involved in the join. If you can preselect certain target rows from your really large tables into smaller temporary tables and build your final result set from them, the query processor will only need to compute a small fraction of the row comparisons it would have had to perform compared to the number of row comparisons necessary to JOIN your original tables. Take this rough math as an example: TABLE A: 1 rows TABLE B: 1 rows SELECT * from A INNER JOIN B ON A.id http://a.id/ = B.A_ic; There are potentially 1 x 1 = 1 (1.0e+08) row combinations to be checked. If instead of joining A to B, we create two derivative tables called C and D (assuming we don't change the column names) TABLE A - TABLE C: 5000 rows TABLE B - TABLE D: 1000 rows SELECT * from C INNER JOIN D ON C.id http://c.id/ = D.A_ic; That means there are now 5000 x 1000 = 500 (5.0e+06) or 1/20th the number of comparisons to run. Computing tables C and D should be in linear or logarithmic time (because you should have good index coverage) so there will usually be a net gain in performance. This is the secret to stepwize result construction. To help you to optimize your particular query, I would need to see it and the table definitions it is working against (SHOW CREATE TABLE works best for me). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach
Re: MySQL LEFT JOIN Optimization Using LIMIT CLAUSE
Try this: [EMAIL PROTECTED]:ule select * from a; ++--+ | id | data | ++--+ | 1 | a| | 2 | b| | 3 | c| | 4 | d| | 5 | e| ++--+ 5 rows in set (0.00 sec) [EMAIL PROTECTED]:ule select * from b; ++--+ | id | data | ++--+ | 1 | aa | | 3 | bb | | 4 | cc | | 3 | bb | ++--+ 4 rows in set (0.00 sec) [EMAIL PROTECTED]:ule select A, a.data, b.id as B, b.data FROM (select a.id as A, a.data from a limit 3) a LEFT JOIN b on A=b.id; +---+--+--+--+ | A | data | B| data | +---+--+--+--+ | 1 | a|1 | aa | | 2 | b| NULL | NULL | | 3 | c|3 | bb | | 3 | c|3 | bb | +---+--+--+--+ 4 rows in set (0.00 sec) -- Augusto Bott augusto.bott (at) gmail.com On 2/2/06, Scott Klarenbach [EMAIL PROTECTED] wrote: I have a table `requirement` which is left joining to a table `inventory` based on a matching `partNumber` column. The inventory table has millions of records, the requirement table has tens of thousands. I'm noticing that the left join between requirement and inventory doesn't take advantage of a LIMIT clause. So whether I select all records from requirement or limit it to 50, the LEFT JOIN operation still seems to be calculating for ALL requirement records against ALL inventory records. (The query takes the exact same amount of time, whether I pull 50 requirement records or 10,000). How can I force mysql to only join the inventory table for the those 50 records brought back by the LIMIT clause? What I would do in a more powerful DB like SQL Server, is build a temporary table with my 50 requirement rows, and then perform the inventory join on the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must have integer constants, not parameters) and View limititations (ie, no indexing of views), I'd have to build this temporary table and the rest of query in PHP first, which is really ugly. I'm hoping there is a nice SQL trick I can use with MySQL to restrict the join to only those records that would come back from the limit set. Thanks, Scott Klarenbach
Re: using IN() clause
On Wed, 17 Nov 2004 21:02:27 -0800 (PST), Mitul Bhammar [EMAIL PROTECTED] wrote: The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future?? Read about MySQL's query optimization here: http://dev.mysql.com/doc/mysql/en/Query_Speed.html The Benchmark() function could also be userful for you. Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using IN() clause
I've a bunch of IDs fetched(around 60,000) from a DB. I'm using these IDs to fetch data from another DB having a related fields in its tables. I'm using IN clause for it. i.e. for e.g. SELECT * FROM site_users WHERE parentUserId IN (1,2,3,4) Again here parentUserId is Indexed. The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future?? __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using IF clause
Hi my table have an field when users can enter any chars If users dont send any content for that field(phone), mysql store ( ) - without quotes in that What im looking for: Display IF phone field is ( ) - NOT ENTER Otherwise display the phone of user. It's possible? Using IF clause Mysql give me an error - maybe IF(`users`.`phone` IS '( ) - ','NOT ENTER',`users`.`phone`) AS phonefield; What's happened? Thanx for your time Diga-me e eu esquecerei Mostre-me e lembrarei Ensina-me e aprenderei - Yahoo! Mail - Participe da pesquisa global sobre o Yahoo! Mail. Clique aqui!
Re: Using IF clause
ColdFusion Lists wrote: Hi my table have an field when users can enter any chars If users dont send any content for that field(phone), mysql store ( ) - without quotes in that What im looking for: Display IF phone field is ( ) - NOT ENTER Otherwise display the phone of user. It's possible? Using IF clause Mysql give me an error - maybe IF(`users`.`phone` IS '( ) - ','NOT ENTER',`users`.`phone`) AS phonefield; What's happened? Thanx for your time In general, you should post your query and the error message you received. That would help us help you. I expect the problem is your use of IS as a comparison operator in place of =. That won't work. IS only works as part of IS NULL or IS NOT NULL. I expect you want something like SELECT IF(users.phone = '( ) - ','NOT ENTER',users.phone) phonefield FROM users; It might be better if you modified your application to store NULL instead of ( ) - when no phone number is entered, or to require a phone number be entered, whichever is appropriate. In the former case (you store NULLs), you would change the above query to: SELECT IF(users.phone IS NULL,'NOT ENTER',users.phone) phonefield FROM users; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables using IN (...) clause returns duplicate rows
I guess I'm a little confused about the MySQL versions. What is the real production version? If 4.0.2 can be called a production version, I'd gladly use it on my web site; however, it doesn't seem to indicate that on the MySQL home page. So if I find a bug in 3.23.47 that was fixed a long time ago as Mr. Milivojevic states, wouldn't I expect it to find its way into the 3.23.X series as the production series? I really like MySQL and don't mean this as any type of a flame, just wondering what the versions mean. Thanks for any insight anyone can shed on this for me Greg Bailey Sinisa Milivojevic wrote: Greg Bailey writes: Description: Joining multiple tables together in a select statement with where clauses using the IN (...) construct generate duplicated output rows. Hi! I tested your case with 4.0.2 and got identical results from both queries. This was some bug probably fixed long time ago. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Joining tables using IN (...) clause returns duplicate rows
On Monday 04 February 2002 01:12 pm, Greg Bailey wrote: What is the real production version? ?If 4.0.2 can be called a production version, I'd gladly use it on my web site; however, it doesn't seem to indicate that on the MySQL home page. ?So if I find a bug in 3.23.47 that was fixed a long time ago as Mr. Milivojevic states, wouldn't I expect it to find its way into the 3.23.X series as the production series? Greg - you are absolutely right. Sinisa should have tested it on a 3.23 version instead. 4.0.2 is not even out. However, your test case worked as expected on 3.23.43-max, our binary. I noticed that your MySQL binary was from source using gcc 2.96, which is very likely the cause of the problem. I will add your test case to our gcc296 test in our test suite - hopefully it will help us find the bug and create a test case for RedHat. In the meantime, I strongly recommend that you switch to our binary. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with using IN Clause, does mysql support it???
Ok, I've been writing SQL for a few years now, but only recenlty started using Mysql. I have a website hosted at a third party that uses mysql 3.23.36 I keep getting errors when I try to run fairly straight forward queries with the IN clause like: select var1 from table1 where var1 not in (select var2 from table2) I also get errors when doing the equivalent on an update: update table1 set var1 = test where var1 in (select var2 from table2) The error I get says: You have an error in your SQL syntax near select var2 from table2 Does mysql support the IN clause? I'm lost without it, particular with updates. Please help! Thanks, Tyson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
