Query takes long time

[sandy1028]

Please help me

$result = mysql_query("select jobid,unix_timestamp(subtime)) from `completedtable`");
                $no_of_rows = mysql_num_rows($result);
        while($r = mysql_fetch_array($result)){
                $exec = '';
                $res = mysql_query("select distinct(exec) from `exectable` where jobid='".$r[0]."'");
                //$res=mysql_query($res1);
                while($row = mysql_fetch_array($res)){
                   $exec .= $row[0]."\n";
                }

The field exec has more than 3 rows so I have used it in while loop. Which takes lots of time to query.
How can I make a query faster.
Two tables has jobid has common field name.

[Piranha]

Well, NEVER EVER use a query in a loop. The code above doesn't contain 2 queries, it contains 101 queries if the first query gives 100 rows back, and all those queries and the communication will take some time. Without knowing how the databases are built I can't help you with the query, please post the table layout with keys, the possible values of exec and what result you want in exec.

[sandy1028]

Two tables with the fields

Completedtable
Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------------------+-------+
| name | varchar(30) | NO | PRI | | |
| jobid | varchar(20) | NO | PRI | |

| subtime | timestamp | NO | | 0000-00-00 00:00:00 | |
+-----------+---------------+------+-----+---------------------+-------

exectable

| name | varchar(30) | YES | MUL | NULL | |
| timestamp | timestamp | NO | | 0000-00-00 00:00:00 | |
| jobid | varchar(20) | YES | | NULL | |
| exec | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------------------+-------+

select * from exectable where jobid='83243.xn01';
+-------------------------+---------------------+------------+---------------------------+
| name | timestamp | jobid | exec |
+-------------------------+---------------------+------------+---------------------------+
| xn | 2008-03-04 14:00:01 | 83243.xn| xn/0 |
| xn | 2008-03-04 14:00:01 | 83243.xn | xn/1 |
| xn| 2008-03-04 14:00:01 | 83243.xn | xn/1 |
| xn | 2008-03-04 14:00:01 | 83243.xn| xn/0 |

For each jobid there is more than 1 exec which I have to display. If I use if statement only one row is fetched not all the rows of that id.

[Piranha]

I think that this will work, but I haven't tested it. If it doesn't work please post again and I will have a look at it when I get home.

select comp.*, exe.exec
from completedtable comp
left join exectable exe on comp.jobid = exe.jobid
group by exe.jobid, exe.exec
order by comp.jobid

I always have problem to decide if group by or order by should be first, try the other one if it doesn't work.

In PHP you should use an if-statement to see if it is the same jobid as the last jobid, and handle differently.

[sandy1028]

The query in mysql database it self is talking too long time After 5 minutes I did not find any rows fetched

[Piranha]

I have checked a few ways to do this, and unfortunatley I can't seem to find a faster query. The time consumed might be because of a couple of things:

1. The query are not optimized. I hope that someone can come up with a better query.

2. The database is not optimized. Make sure to have the right columns indexed.

3. To many rows in the tables. How many rows are there in the two tables?

Of course all these things influences, but it could be good to test them once at a time.

[Sxooter]

Piranha, your query looks pretty straight forward. I'd worry about whether or not the fields in the tables that are being used to join and group by are properly indexed.

comp.jobid, exe.jobid, exe.exec should all three be indexed. A multi column index on

exe.jobid and exe.exec might make sense too.