Is anything faster than COUNT

Hi, I need some help with one SQL query that I need to run on MySQL, it uses the COUNT and CASE WHEN statements, so when I run it directly on the MySQL interfaces it takes about 5 minutes to run, this is slow because it only returns 29 rows and it seeks and SUM in 16661 rows on 4 different tables, i need to run it from a vb program so the process would take another 4 or 5 minutes more.
The critical part of the query looks like this:
((COUNT(case when idtipo=1 and idtorre=1 and idpais=1 and week(fechasalida,1)=19 and year(fechasalida)=2002 then 1 end)*10) + (COUNT(case when idtipo=2 and idtorre=1 and idpais=1 and week(fechasalida,1)=19 and year(fechasalida)=2002 then 1 end)*7.5) + (COUNT(case when idtipo=3 and idtorre=1 and idpais=1 and week(fechasalida,1)=19 and year(fechasalida)=2002 then 1 end)*5) + (COUNT(case when idtipo=4 and idtorre=1 and idpais=1 and week(fechasalida,1)=19 and year(fechasalida)=2002 then 1 end)*2.5)) / (COUNT(case when idpais=1 and idtorre=1 and week(fechasalida,1)=19 and year(fechasalida)=2002 then 1 end)) as CALIF_NAC_1

Can anybody help me to optimize this?

i think i speak for everyone when i say, holly ****.

what the hell is this for???? cracking US secret codes??? lol

just a basic idea, that way we can think of another way to handle this one.

Hmmmm,
Your right about the code being slow. Is there any way you can use C++ for this because the C++ MySQL API is faster than MyODBC, which is what I assume you're using from VB?

Try segmenting the code. Here's something to test: For the values of the case statements, seperate the query into 2-3 queries and collate the results to form the final query. Depending on the way MyODBC is setup, this *MAY* give you a performance boost.

If that doesnt work, try working your query into sections and then breaking down each one into smaller pieces of code. Run each section through MySQL, and try and find the slowest section. Post that section here and i'll help you optimize it.

I have a program where we store the suggestions from our customers, so the program uses 7 tables to make this query, the query's suposed to get the count of 4 different kinds of comments we receive from the customers, sumarize them and make a qualification of each building, buildings are what we get the comments about, so the result recodset would look like this:

question| qual_buil_1| qual_buil_2 |qual_build_3| qual_build_4

i hope it's a little more clear now

hmmm ok. 7 tables...? sounds like a big spread of data for such a small task? can you post the struct of the tables here? might help to get a better idea.

I took a second look at the tables I was quering and there were 3 of them that i really didn't need the info from them, so my final query looks like this:
SELECT COUNT(IDTIPO) as cuenta FROM COMENTARIOS,TORRES_HABS,HUESPEDES,PAISES WHERE WEEK(FECHASALIDA,1)=19 AND YEAR(FECHASALIDA)=2002 AND IDPREGUNTA=4 AND IDTIPO=1 AND IDTORRE=4 AND HUESPEDES.IDPAIS=1 AND HUESPEDES.HABITACION=TORRES_HABS.HABITACION AND HUESPEDES.IDHUESPED=COMENTARIOS.IDHUESPED AND HUESPEDES.IDPAIS=PAISES.IDPAIS;

but i think it's a little slow yet, it takes 2.63 secs and I need to run it 120 times.
the tables structures are:
mysql> deScribe COMENTARIOS;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| IdPregunta | tinyint(4) | | | 0 | |
| IdHuesped | smallint(6) | | | 0 | |
| IdTipo | tinyint(4) | | | 0 | |
+------------+-------------+------+-----+---------+-------+

mysql> deScribe TORRES_HABS;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| IdTorre | smallint(6) | | | 0 | |
| Habitacion | varchar(10) | | | | |
+------------+-------------+------+-----+---------+-------+
mysql> deScribe HUESPEDES;
+--------------+--------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+------------+-------+
| IdHuesped | smallint(6) | | PRI | 0 | |
| Nombre | varchar(80) | | | | |
| Habitacion | varchar(10) | | | | |
| FechaLlegada | date | YES | | NULL | |
| FechaSalida | date | | | 0000-00-00 | |
| Domicilio | varchar(100) | YES | | NULL | |
| Ciudad | varchar(20) | YES | | NULL | |
| Estado | varchar(20) | YES | | NULL | |
| Pais | varchar(20) | YES | | NULL | |
| Telefono | varchar(25) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| Edad | varchar(5) | YES | | NULL | |
| IdPais | smallint(6) | YES | | NULL | |
+--------------+--------------+------+-----+------------+-------+
mysql> deScribe PAISES;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| IdPais | smallint(6) | | PRI | 0 | |
| Nombre | varchar(30) | | | | |
+--------+-------------+------+-----+---------+-------+