joining small table with large table

MySQL & MariaDB Other MySQL® Questions
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

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.

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.

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.

5

Check mysql documentation, look at [URL]http://www.xaprb.com/blog[/URL] 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 )