RE: Which is better
I hope the former better. Test with query plan output, though Best Rgs, Shafi AHMED -Original Message- From: Adarsh Sharma [mailto:adarsh.sha...@orkash.com] Sent: Tuesday, August 02, 2011 12:12 PM To: mysql@lists.mysql.com Subject: Which is better Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) B ( 7 MB ) A has 10 columns B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=shafi.ah...@sifycorp.com Get your world in your inbox! Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id! Log on to http://www.sify.com ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Technologies Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at ad...@sifycorp.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: Which is better
is optimizer depend on size of table? not sure On Mon, Aug 1, 2011 at 11:48 PM, Shafi AHMED shafi.ah...@sifycorp.comwrote: I hope the former better. Test with query plan output, though Best Rgs, Shafi AHMED -Original Message- From: Adarsh Sharma [mailto:adarsh.sha...@orkash.com] Sent: Tuesday, August 02, 2011 12:12 PM To: mysql@lists.mysql.com Subject: Which is better Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) B ( 7 MB ) A has 10 columns B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=shafi.ah...@sifycorp.com Get your world in your inbox! Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id! Log on to http://www.sify.com ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Technologies Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Which is better
On 8/2/2011 02:41, Adarsh Sharma wrote: Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) B ( 7 MB ) A has 10 columns B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks There is no difference in performance. The optimizer will change the sequence it uses to read the tables according to its own rules. If you had used STRAIGHT JOIN to force a particular execution path, the it would normally be faster to read the smaller table first. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Which is better
Hard to tell. It depends on the cardinality of tables' id (I assume the IDs are not unique in each of the tables). David. -Original Message- From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, August 02, 2011 2:47 PM To: Adarsh Sharma Cc: mysql@lists.mysql.com Subject: Re: Which is better On 8/2/2011 02:41, Adarsh Sharma wrote: Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) B ( 7 MB ) A has 10 columns B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks There is no difference in performance. The optimizer will change the sequence it uses to read the tables according to its own rules. If you had used STRAIGHT JOIN to force a particular execution path, the it would normally be faster to read the smaller table first. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dle...@us.univision.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: which is better long rows in table or two short row tables
Not sure what your question is, but remember you can use indexes to speed up access to rows. Thus usually the less fields you need to seach in the better, avoid OR searches across fields. - Original Message - From: abhishek jain [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 13, 2006 7:54 AM Subject: which is better long rows in table or two short row tables Dear Friends, I was to create a site with quite some heavy mySQL database. I wanted to know which is better longer rows in a table or two short rows tables. When compared in terms of speed etc. Pl. help me , with this question and any other tip you may find can be useful to me. Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which is better long rows in table or two short row tables
At 08:54 AM 7/13/2006, abhishek jain wrote: Dear Friends, I was to create a site with quite some heavy mySQL database. I wanted to know which is better longer rows in a table or two short rows tables. When compared in terms of speed etc. Pl. help me , with this question and any other tip you may find can be useful to me. Thanks, Abhishek jain Properly denormalized data with appropriate use of indexes. Google for relational data denormalise. Without knowing your data, whether the application is heavy on INSERTs and UPDATEs, or if it is mostly SELECTs, it is impossible to answer your question. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.9.10/387 - Release Date: 7/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is better: big SQL statement or bigger db?
Hi,
May the following sql statement is more efficient.
SELECT * FROM bc_posts WHERE
post_citysoundex IN('A265','A415',.)
Armand
motorpsychkill wrote:
I need to have a user input a city and have MySQL pull up any records with
that city OR nearby cities (within 10 mi). Which of the following would be
the most efficient way to do this:
Case A:
When a user enters a city, an array of nearby cities is created so that an
SQL statement like the following is generated:
SELECT * FROM bc_posts WHERE
post_citysoundex = 'A265' OR
post_citysoundex = 'A415' OR
post_citysoundex = 'A453' OR
post_citysoundex = 'A430' OR
post_citysoundex = 'A624' OR
post_citysoundex = 'A350' OR
.
.
.
This statement would probably be much larger (upto 150 lines) and would
query one table without additional joins.
Case B:
Here, when a user enters a city, the soundex of it is created and then
queries a table that contains every city in the db PLUS all surrounding
cities (calculated and inserted with each new city insert). Obviously, here
the table would get large while my actual SQL statement is pretty
straightforward but would require a join.
I'm not sure which of these is the more elegant approach or would scale up
much easier. Any input from the DB gurus would be appreciated! Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is better: big SQL statement or bigger db?
* [EMAIL PROTECTED] Case A: ... This statement would probably be much larger (upto 150 lines) and would query one table without additional joins. Case B: Here, when a user enters a city, the soundex of it is created and then queries a table that contains every city in the db PLUS all surrounding cities (calculated and inserted with each new city insert). Obviously, here the table would get large while my actual SQL statement is pretty straightforward but would require a join. I'm not sure which of these is the more elegant approach or would scale up much easier. Any input from the DB gurus would be appreciated! Thanks! Most elegant is case B I would think. Let the database system do the work it is designed for. Scalability and performance in mysql I do not know. What about a test? -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
