how to join part of two table, rather than whole

Question

i was just wondering that in case we have two tables in Mysql and both have millions of records. so we want to join part of two table. how can we accomplish that. according to me if we use limit then it first queries all records and then limit the result. will appreciate your suggestions. thanks

consider there are two tables. user:(id in autoincrement) id, name, email, gender

user_actions:(user_id is foreign key here) id, user_id, action_id, action_date

we need to query actions for user but not all at a time, as the data is huge in the tables

Answer 1

You should add an index on the date field, and then use a where clause to say less than one date and greater than another. The index should allow you to not search through all records in the limit.

Answer 2

If you want to show the result to end user you can use a paging strategy to show the result page by page (50 rows per page for example). You can use limit keyword of mysql to produce each page.

you can find the example here.

Answer 3

You can use a WHERE clause to limit the records used in the join. You'll have to provide more details about how you want to choose the part of the table if you want more details.