Adding Limit speeds up mysql query , WHY?

Question

I have a simple QUERY with an INNER JOIN. The tables that are joined have 1500 en 2500 rows. Both tables have a primary key. The join is not done on this key.

The result set of this query has 1500rows. When I run this query it takes 20 seconds... (AN ETERNITY)

SELECT so.order_id
FROM   shop_orders so
       INNER JOIN shop_order_payments sop
          ON so.order_code = sop.order_code
WHERE  sop.status = 9
ORDER  BY date_created DESC

When I then add the " LIMIT 1500 " at the end , the query takes only 1sec. (When I remove it it takes again 20 seconds so it's not the cache...)

Why is this ?
And is there a fast way to find out how many rows this query has as a result?

note: I run this query in a php script with the mysql() function )

*update1: The result of the EXPLAIN

id   select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  sop ALL NULL    NULL    NULL    NULL    1153    Using where; Using temporary; Using filesort
1   SIMPLE  so  ALL NULL    NULL    NULL    NULL    2529    Using where

Update 2:

I ran DESC as requested in the comments


payment_id - int(11)    NO  PRI     auto_increment
timestamp - timestamp   NO      CURRENT_TIMESTAMP   
order_code - varchar(100)NO         
acceptance - varchar(50) NO         
amount  -  varchar(20)  NO

So only a primary index shows up, this is not enough as an index?

the query takes long time time to run because maybe you lack indexes. can you the result of this query? DESC shop_orders and DESC shop_order_payments ? — John Woo
What indexes do you have? Can you show the output of SHOW CREATE TABLE ... for each table? — Mark Byers

John Woo John Woo · Accepted Answer · 2013-01-12T12:25:23

The EXPLAIN tells that the query is using no index. Thus it performs fulltable scan causing it to be slow.

try to run this statements,

ALTER TABLE shop_orders ADD INDEX td_idx (order_code);
ALTER TABLE shop_order_payments ADD INDEX td2_idx (order_code);

and run the SELECT query again.

UPDATE 1

Your table has an index but on column payment_id which in this case isn't used since the join is using column order_code. You must also define an index on that column.

Adding Limit speeds up mysql query , WHY?

1 Answers