Slow query, must be a better way

[galorin]

I use the following SELECT to create a view to get a total of jobs under a certain quote, and a total of jobs that have been completed.

select 
h.quote_id as quote_id
, count(h.quote_id) as qcount
, count(c.HT_ID) as ccount 
from HouseType as h
left join HTCheck as c on h.ht_id = c.HT_ID
group by h.quote_id

I use the following query to get a list of all quotes under a new style (no set design_production_number) where the jobs have not been completed. It takes 20 seconds to run and currently returns 30 rows

select distinct c.quote_id from CheckedQuotes as c
left join HouseType as h 
on h.quote_id = c.quote
where qcount != ccount
and h.design_production_no is null;

A change of the last line to is not null does a search for the old style jobs, and returns 796 rows in about 3 seconds. Still too slow for my Users. I think I have a logical flaw as well, assuming that of the 2k+ quotes we've done we have only completed about half of them, (a quick investigation provides a few jobs worth £20k as uncompleted, hence unbilled) yet we re miraculously still in business. A colleague has suggested that I don't have logical errors but that a combination of scrubbing some bad data and bad data entry has resulted in a high error rate, but that can be fixed after the fact. I'm just after more speed.

 

[ingresman]

Do you have an index on the id column of both tables ?
Can you do a desc tablename on both the tables and post them here.
Also if you can do explain extended before the select we should be able to see what mysql thinks it's going to do.

 

[galorin]

HouseType table, the ht_id is the primary key, and after adding an index to HTCheck.HT_ID I have seen a significant speed increase for old style searches. I have also modified the query for populating the Quote_ID fields so it is

SELECT c.quote FROM CheckedQuotes as c
left join quotations as q
on c.quote = q.quote_id
where qcount != ccount
and q.enquirer is not null
order by c.quote desc

which runs significantly faster, but still provides ~1 second delay which people have been complaining about.

here is the requested desc tablename

mysql> desc HouseType;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| quote_id             | int(10) unsigned | YES  |     | NULL    |                |
| ht_id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| house_type           | varchar(255)     | YES  | MUL | NULL    |                |
| lap_dwg              | varchar(255)     | YES  |     | NULL    |                |
| uap_dwg              | varchar(255)     | YES  |     | NULL    |                |
| design_production_no | varchar(255)     | YES  | MUL | NULL    |                |
| approved             | date             | YES  |     | NULL    |                |
| SoftVer              | varchar(5)       | YES  |     | NULL    |                |
| Builder_ID           | int(10) unsigned | YES  |     | NULL    |                |
| Floor                | varchar(45)      | YES  |     | NULL    |                |
| Revision             | varchar(45)      | YES  |     | 0       |                |
| Dist_ID              | int(10) unsigned | YES  |     | NULL    |                |
| Enq_ID               | int(10) unsigned | YES  |     | NULL    |                |
| Hours                | float(4,2)       | YES  |     | 0.00    |                |
| designer             | varchar(50)      | YES  |     | NULL    |                |
| deleted              | int(1) unsigned  | NO   |     | 0       |                |
+----------------------+------------------+------+-----+---------+----------------+

mysql> desc HTCheck;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| CheckID      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Ready_Date   | date             | YES  |     | NULL    |                |
| Ready_By     | varchar(50)      | YES  |     | NULL    |                |
| Checked_By   | varchar(50)      | YES  |     | NULL    |                |
| HT_ID        | int(10) unsigned | YES  | MUL | NULL    |                |
| Checked_Date | date             | YES  |     | NULL    |                |
| exempt       | int(1) unsigned  | NO   |     | 0       |                |
+--------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

and explain extended for my new query

mysql> explain extended SELECT c.quote FROM CheckedQuotes as c
    -> left join quotations as q
    -> on c.quote = q.quote_id
    -> where qcount != ccount
    -> and q.enquirer is not null
    -> order by c.quote desc;
+----+-------------+------------+--------+------------------+----------+---------+------------------+-------+---------------------------------+
| id | select_type | table      | type   | possible_keys    | key      | key_len | ref              | rows  | Extra                           |
+----+-------------+------------+--------+------------------+----------+---------+------------------+-------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL             | NULL     | NULL    | NULL             |  2260 | Using where; Using filesort     |
|  1 | PRIMARY     | q          | eq_ref | PRIMARY,enquirer | PRIMARY  | 4       | c.quote          |     1 | Using where                     |
|  2 | DERIVED     | h          | ALL    | NULL             | NULL     | NULL    | NULL             | 12404 | Using temporary; Using filesort |
|  2 | DERIVED     | c          | ref    | HT_Index         | HT_Index | 5       | contacts.h.ht_id |     1 | Using index                     |
+----+-------------+------------+--------+------------------+----------+---------+------------------+-------+---------------------------------+
4 rows in set, 1 warning (0.13 sec)

Sorry about the formatting, didn't paste very pretty.

 

[ingresman]

Hi,
Is the query on your test server as it seems to show 0.13 seconds which is a lot faster than 1.0 seconds.
I don't know if I missed something but you have two tables
checkedquotes and quotations but the desc's you sent are for Housetype and HTcheck.
the plan looks to show thre table alisaes i.e. q, h and c so I'm a bit confused.
have I missed something ?

 

[galorin]

I'm afraid I was tweaking the server and re-writing my queries while typing out the above. Most of the problems have resolved themselves as a result of this. I have found a network bottleneck as a result of all the testing and tweaking. That was slowing down the end-user experience.

Apologies for the wild goose chase.

 

[ingresman]

no worries ,as long as you got sorted and thanks for telling us what happend in the end ! (lots don't !)