[SOLVED] Query Performance

[charafantah]

I have this SQL Query that takes 10-15 minutes to execute, is there a better way to write so i can minimize execution time?

SELECT ttemp.t24no, ttemp.subasset,ttemp.description,ttemp.inputter,ttemp.authoriser,temp.telekurs,temp.price,ttemp.price,ttemp.sxc,ttemp.pdate,temp.pdate,temp.ccy
FROM temp
INNER JOIN ttemp
ON temp.telekurs=ttemp.telekurs and temp.sxc=ttemp.sxc and temp.ccy=ttemp.ccy and temp.price<>ttemp.price

 

The average rows in temp is 100,000 rows and average rows on ttemp is 20,000 rows

 

Thanks,

 

[kickstart]

Hi

 

Nothing complicated there, although I might be tempted to try moving the temp.price<>ttemp.price into a WHERE clause.

 

Are the columns indexed? How much data do you expect to get back?

 

All the best

 

Keith

[markwillis82]

can you place EXPLAIN at the start of the query and post back the results. you may not be using indexes (resulting in lots of table scans)

 

[charafantah]

thats the result of the explain:

+----+-------------+-------+------+---------------+------+---------+------+-----
--+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
  | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+-----
--+--------------------------------+
|  1 | SIMPLE      | ttemp | ALL  | NULL          | NULL | NULL    | NULL | 1594
5 |                                |
|  1 | SIMPLE      | temp  | ALL  | NULL          | NULL | NULL    | NULL | 9884
9 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+-----
--+--------------------------------+
2 rows in set (0.19 sec)

 

 

i expect the result to be around 100 rows...nope the column are not indexed....i dont have any unique thing to index (is it still possible to do it? :S)

 

 

[kickstart]

Hi

 

Indexes do not need to be unique, They are used to reduce the need for searches.

 

All the best

 

Keith

[markwillis82]

If you want - try adding 1 index to each table containing the 4 where columns. So the index would be on:  telekurs,sxc, ccy and price.

 

Run the query again and time it + show us an EXPLAIN again

[charafantah]

WOW!!! i guess i should RTFM!   after putting the indexes, the query takes 1.5 seconds!!!  thanks guys

 

here's the explain:

+----+-------------+-------+------+------------------------------+----------+---
------+-------------------+-------+-------------+
| id | select_type | table | type | possible_keys                | key      | ke
y_len | ref               | rows  | Extra       |
+----+-------------+-------+------+------------------------------+----------+---
------+-------------------+-------+-------------+
|  1 | SIMPLE      | temp  | ALL  | telekurs,ccy,sxc,ccy_2,sxc_2 | NULL     | NU
LL    | NULL              | 98849 |             |
|  1 | SIMPLE      | ttemp | ref  | ccy,sxc,telekurs             | telekurs | 17
      | t24.temp.telekurs |     1 | Using where |
+----+-------------+-------+------+------------------------------+----------+---
------+-------------------+-------+-------------+
2 rows in set (0.00 sec)

 

[markwillis82]

thats much better. Do you have any other indexes on that table? as you may be able to modify an old index instead of creating a new one.

 

glad i could help