How to optimize MySQL queries and decrease server load?

My db tables look like that

First table:

Second table - lastsrvc (Last service info). id column -uniques auto-incremented id for every issue. 'cid' column - company's id. It's the link column between 2 tables: main and lastsrvc.

Now, what I want to do is, lets say, count all issues for company 1. My code sends before while 1 query and fetches all rows of main table. Then sends second query within while for every company's row.

If in the future there will be 10 000 company's it will load server I think. Is there any way to send 1 query instead of sending query for every company (within while)? Is it possible to use COUNT before while, instead of second query within while?

My code looks like that

    <?php
    $query=$db->query("SELECT * FROM main");
    while($row=$query->fetch_object())
    {?>
        <tr> 
        <td><?=$row->id;?></td>
        <td><?=$row->company;?></td>
        <td><?=$row->address;?></td>
        <td><?=$row->contact_name;?></td>
        <td><?=$row-&开发者_C百科gt;contact_phone;?></td>
        <td><?php 
        if ($row->warr)
        {
            echo 'Var,'.$row->warr_year.' il';
        }
        else {
            echo 'Yoxdur';
            }
        ?></td>
        <td>
        <?php
        if ($query2=$db->query("SELECT * FROM lastsrvc WHERE cid='$row->id'"))
        echo $query2->num_rows;
        ?>
        </td>
        </tr>
<?php } ?>

Use this as your top-level (and only) query:

SELECT main.id, company, address, contact_name, contact_phone, COUNT(cid) as cnt
FROM main
LEFT JOIN lastsrvc ON main.id = lastsrvc.cid
GROUP BY main.id

One single query with a join is almost always going to be much more efficient than running 1+n queries. You just echo out that 'cnt' field as usual:

<td><?php echo $row->cnt ?></td>

Do a JOIN

SELECT main.etc, main.etc ..., COUNT(*) AS total
FROM main
LEFT JOIN lastsrvc ON main.id=lastsrvc.cid
GROUP BY main.id;

When the data becoming bigger, use pagination to fragment the result.
As the larger HTML return, browser also cannot take it
(Imagine a page more than 10Mb is hard to load properly in browser)

You can also stored the results into cache, such as into memcache.
The benefits of cache is avoid excessive query to database

In addition to the other answers, you can also set indices on the columns you're joining on and filtering to speed up queries later on.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html