Why is this Query taking so long to retrieve data than the other?

[nirvana]

Hi All,

I am new in this area and have a limited knowledge.
Please look at the following two queries which retreive different data points from the same tables. I am puzzled by the time taken to get the data. I am using MySQL database and PHP web interface.
The problem started when my graph plotting with JpGraph took a long time and didn't return any graph. It was working fine before and suddenly I didn't get any graph output if the data I queried is more than 700 points on the first query.
The query is called 50 times and the graphs are superpositioned together. So to get one graph, 8080 rows need to be retrieved.
Before even though it took a while, at least I get the output graph.

When I investigated it, I found this:

SELECT measured_para_1, measured_para_5
FROM tested_device join dwp_data join dwp_data_container where tested_device.device_num = dwp_data.device_num AND
dwp_data.test_number = dwp_data_container.test_number AND device_ID = 7660 AND TestGroup = 2 AND
parameter = 'spec' AND port = 1 AND attn = 0 AND measured_para_2 = 39 AND temp = 25
This query took 10.23sec to retrieve 159 rows

This second query is called 9 times, altogether 40509 rows need to be retrieved to get one graph which includes 9 superpositioned graphs. It is strange that this query still works and gives output graph although the number of rows are much more than the first one.
SELECT measured_para_2, measured_para_3
FROM tested_device join dwp_data join dwp_data_container where tested_device.device_num = dwp_data.device_num AND
dwp_data.test_number = dwp_data_container.test_number AND device_ID = 7660 AND parameter = 'xtalk' AND TestGroup = 2
AND measured_para_1 = 1 AND port = 5 AND attn = 0 AND temp = 25
This query took 3.16sec to retrieve 1000 rows

Do you guys know why? Is it because there are so many data in the database and that's why taking so long?
Is there a way to speed this up? Please let me know if more information is needed.

thanks in advance,
Nirvana

[artacus]

Always use ON with JOIN.
Otherwise the number of records it has to go thru is multiplied by the number of records in that table.

[code]
SELECT measured_para_2, measured_para_3
FROM tested_device AS dev
JOIN dwp_data AS data ON dev.device_num =  data.device_num
JOIN dwp_data_container AS dat_con ON data.test_number = dat_con.test_number
WHERE  dev.device_ID = 7660 AND parameter = 'xtalk' AND TestGroup = 2
AND measured_para_1 = 1 AND port = 5 AND attn = 0 AND temp = 25[/code]

[artacus]

Oh, and to qualify my always us ON statement. If you do need to join every record in one table to every record in another, you can use "JOIN ON 1=1"

[fenway]

If it's still slow, post the EXPLAIN.

[nirvana]

Hi,
Thanks for the help and sorry for late response as I was away.
I did try the code with join on but it's still slow, the similar timing as before.

Can you please explain?

thanks in advance,

[quote author=fenway link=topic=117558.msg481537#msg481537 date=1165632697]
If it's still slow, post the EXPLAIN.
[/quote]

[fenway]

Run the SELECT query with "EXPLAIN" in front of it, and post the output.

[nirvana]

Hi

The following is the output:
I copy and paste straight out from the database and not suer the table alignment will be shifted.

thanks for your help.

regards,


+----+-------------+--------------------+--------+---------------+---------+---------+----------------------------------------------+----------+-------------+
| id | select_type | table              | type   | possible_keys | key     | key_len | ref                                          | rows     | Extra       |
+----+-------------+--------------------+--------+---------------+---------+---------+----------------------------------------------+----------+-------------+
|  1 | SIMPLE      | dwp_data_container | ALL    | [NULL]        | [NULL]  |  [NULL] | [NULL]                                       | 24610672 | Using where |
|  1 | SIMPLE      | dwp_data           | eq_ref | PRIMARY       | PRIMARY |       4 | manufacturing.dwp_data_container.test_number |        1 | Using where |
|  1 | SIMPLE      | tested_device      | eq_ref | PRIMARY       | PRIMARY |       4 | manufacturing.dwp_data.device_num            |        1 | Using where |
+----+-------------+--------------------+--------+---------------+---------+---------+----------------------------------------------+----------+-------------+


[quote author=fenway link=topic=117558.msg498957#msg498957 date=1168179140]
Run the SELECT query with "EXPLAIN" in front of it, and post the output.
[/quote]

[fenway]

You're clearly missing appropriate indexes on "dwp_data_container"... 24610672 rows examined?!??