•  

Announcement

Announcement Module
Collapse
No announcement yet.

joining small table with large table

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • joining small table with large table

    #1
    i have two tables

    Table 1 users it have 100 records
    user_id,username

    Table 2 payments have 9000 records
    payment_id,user_id

    i have two query which is the best

    Query1
    select users.username from users,payments where users.user_id=payments.user_id

    Query2
    select users.username from users,payments where payments.user_id=users.user_id
    Tags: None

  • #2
    In this case it doesn't matter.

    The order of the columns in a join condition has nothing to do with in which order the two tables actually are joined.

    BTW: I suggest you to use the newer INNER JOIN syntax:

    SELECT users.usernameFROM users uINNER JOIN payments p ON u.user_id = p.user_id

    This way the chance to forget a join condition is virtually zero and it is compatible with LEFT JOIN syntax.
    So it's just good practice to use this syntax.

    Comment


    • #3
      Query3
      select users.username from users inner join payments on users.user_id=payments.user_id

      Add explain to queries (explain select ...) and check amount of the rows fetched by mysql.

      Comment


      • #4
        i am typing explain or describe before query but dont understand what its means where can i learn about explain command. I am using Mysql Query Browser.

        Comment


        • #5
          Check mysql documentation, look at http://www.xaprb.com/blog and http://www.mysqlperformanceblog.com

          The worst case: mysql has to check all records from tables used in query. The best case: mysql fetches only those rows that meet criteria.

          You can check how good query is by multiplying values in rows column. Very big result means that there is something wrong with query or with indexes in the tables ( possible_key = NULL, key = NULL, type = ALL )

          Comment

          Working...
          X