slow loop problem

marsil

Hello,

I have two MySql tables with more than a thousand rows each.

I have to check each row of table 1 against each row of table 2, until I find a match. I am using a while loop to do that, but it gets VERY slow. Is there any faster way to check one table against the other so I can find repeated entries?

Thanks,

Marcelo

Oersoep

1) Use a JOIN query. You can find examples in the mysql docs.

Using a join you let de the database program put 2 or more tables together in a way you tell it to.

This one for example will fetch all fitting rows:


SELECT

table1.*,   /* any fields you like from any of the tables */
table2.*    /* just keep the field names unique */

FROM   table1

LEFT JOIN table2
ON table2.field_a = table1.field_b

WHERE
table2.field_b IS NOT NULL /* kick out the non-matches */

You can probably make the values from table1 come forward only once by fooling around with the distinct keyword.

2) Make the fields you compare of the same type and size. The database will match the values from the different tables faster.

3) Create indexes on the tables. (It's something like pre-loading the fields that will be compared. Much faster than without.)

You can see if you got it right, by running your query with the EXPLAIN word.

EXPLAIN SELECT * FROM sometable WHERE anything = 'something'\\G

( The G on theend should be a backslash and a G. It got filterede.
Blackslash-G instead of a semicolon (😉 will format the output as below.)

You'll see something like this:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: domein
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 36084
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: klant
         type: eq_ref
possible_keys: PRIMARY,klantnr
          key: PRIMARY
      key_len: 4
          ref: adminarge.domein.domeinnr
         rows: 1
        Extra:
2 rows in set (0.00 sec)

Multiply all values in the 'rows' column. That's how much times the database server needs to compare rows. Try to reach 1 big number and 1 or more of ones.