vendredi 27 juillet 2018

Select Random Rows in MySQL for JOIN with another table

I am migrating millions of row from table1 to table2. After the migration, I am trying to spot check certain rows to see if they are migrated properly. Here is a sample SQL query [not the exact one]:

SELECT     tbl1.name,
           tbl1.address,
           tbl2.name,
           tbl2.new_address
FROM       
          (SELECT * 
           FROM table1
           ORDER BY   RAND() limit 10) tbl1
INNER JOIN table2 tbl2
ON         tbl1.name = tbl2.name;

I am trying to select 10 rows from table 1 to join with table2 for checking. If I ran this query with an explain, it does a full table scan i.e., all rows in table 1 (which is > 130 million). How to optimize this query in MySQL?




Aucun commentaire:

Enregistrer un commentaire