MySQL Returning the top 5 of each make

Databases
1

Hi.

I want to be able to return 5 records per make. I’ve tried several queries but had no luck. Here’s what I have but is too damn slow and hangs most of the times:


SELECT a.idmarca, a.idmodelo, a.versao, a.cilindrada, a.potencia, a.portas, a.id, a.ano, a.cor, a.kms, a.preco, u.localidade, u.telefone, u.telemovel, a.data, a.foto1
FROM anuncios_auto a INNER JOIN utilizadores u ON u.username = a.utilizador
WHERE a.idmarca IN ($idsmarcas) AND a.activo = '1'
AND ( SELECT COUNT(*)
FROM anuncios_auto
WHERE idmarca IN ($idsmarcas) AND activo = '1' AND ano <> 0 AND kms <> 0 AND combustivel <> '' and cor <> '' AND (foto1 <> 'semfoto.gif' AND foto1 <> '' AND foto1 IS NOT NULL) AND data BETWEEN '$newdate' AND '$today' 
AND idmarca = a.idmarca
AND data > a.data GROUP BY idmarca)
 < 5
 AND (u.telefone <> '' OR u.telemovel <> '' OR u.telefone IS NOT NULL OR u.telemovel IS NOT NULL)
ORDER BY a.idmarca ASC, a.data DESC

$idsmarcas is a comma seperated string with car makes. Ex: ‘Audi’,‘BMW’,‘Honda’,‘Mazda’,

$newdate and $today are date values.

Please help me optimize this query.

Thanks.

2

could you plesae run the following two EXPLAINs –

EXPLAIN
SELECT a.idmarca
     , a.idmodelo
     , a.versao
     , a.cilindrada
     , a.potencia
     , a.portas
     , a.id
     , a.ano
     , a.cor
     , a.kms
     , a.preco
     , u.localidade
     , u.telefone
     , u.telemovel
     , a.data
     , a.foto1
  FROM anuncios_auto a 
INNER 
  JOIN utilizadores u 
    ON u.username = a.utilizador
   AND (u.telefone <> '' 
     OR u.telemovel <> '' 
     OR u.telefone IS NOT NULL 
     OR u.telemovel IS NOT NULL )
 WHERE a.idmarca IN ($idsmarcas) 
   AND a.activo = '1'
ORDER 
    BY a.idmarca ASC
     , a.data DESC

EXPLAIN
SELECT a.idmarca
     , a.idmodelo
     , a.versao
     , a.cilindrada
     , a.potencia
     , a.portas
     , a.id
     , a.ano
     , a.cor
     , a.kms
     , a.preco
     , u.localidade
     , u.telefone
     , u.telemovel
     , a.data
     , a.foto1
  FROM anuncios_auto a 
INNER 
  JOIN utilizadores u 
    ON u.username = a.utilizador
   AND (u.telefone <> '' 
     OR u.telemovel <> '' 
     OR u.telefone IS NOT NULL 
     OR u.telemovel IS NOT NULL )
 WHERE a.idmarca IN ($idsmarcas) 
   AND a.activo = '1'
   AND ( SELECT COUNT(*)
           FROM anuncios_auto
          WHERE idmarca IN ($idsmarcas) 
            AND activo = '1' 
            AND ano <> 0 
            AND kms <> 0 
            AND combustivel <> '' 
            AND cor <> '' 
            AND (foto1 <> 'semfoto.gif' 
            AND foto1 <> '' 
            AND foto1 IS NOT NULL) 
            AND data BETWEEN '$newdate' AND '$today' 
            AND idmarca = a.idmarca
            AND data > a.data 
         GROUP  
             BY idmarca) < 5
ORDER 
    BY a.idmarca ASC
     , a.data DESC
3

The first explain returns:

id select_type table type possible_keys key key_len ref rows extra
1 SIMPLE u ALL NULL NULL NULL NULL 959 Using Where; Using temporary; Using filesort

1 SIMPLE a ALL NULL NULL NULL NULL 3100 Using Where; Using join buffer

The second explain returns:

id select_type table type possible_keys key key_len ref rows extra
1 PRIMARY u ALL NULL NULL NULL NULL 959 Using Where; Using temporary; Using filesort

1 PRIMARY a ALL NULL NULL NULL NULL 3100
Using Where; Using join buffer

1 DEPENDENT SUBQUERY anuncios_auto ALL NULL NULL NULL NULL 3100 Using Where; Using temporary; Using filesort

4

any chance you could format those results so that we can read them with the column headers over the data?

5

Reformatted

The first explain returns:

id: 1
select_type: SIMPLE
table: u
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 959
extra: Using Where; Using temporary; Using filesort

id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3100
extra: Using Where; Using join buffer

The second explain returns:

id: 1
select_type: PRIMARY
table: u
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 959
extra: Using Where; Using temporary; Using filesort

id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3100
extra: Using Where; Using join buffer

id: 1
select_type: DEPENDENT SUBQUERY
table: anuncios_auto
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3100
extra: Using Where; Using temporary; Using filesort

Hope it’s more readable now.

6

thank you for the effort, but it’s a bit worse like that :slight_smile:

i was hoping for this –

id  select_type  table  type  possible_keys  key   key_len  ref   rows   extra
1   SIMPLE       u      ALL   NULL           NULL  NULL     NULL   959   Using Where; Using temporary; Using filesort
1   SIMPLE       a      ALL   NULL           NULL  NULL     NULL  3100   Using Where; Using join buffer


id  select_type         table          type  possible_keys  key   key_len  ref   rows   extra
1   PRIMARY             u              ALL   NULL           NULL  NULL     NULL   959   Using Where; Using temporary; Using filesort
1   PRIMARY             a              ALL   NULL           NULL  NULL     NULL  3100   Using Where; Using join buffer
1   DEPENDENT SUBQUERY  anuncios_auto  ALL   NULL           NULL  NULL     NULL  3100   Using Where; Using temporary; Using filesort

i’m not really the expert when it comes to analyzing EXPLAINs, but you can see even in the first one, where there is no “top 5” going on, that there is a table scan

try adding an index on anuncios_auto.idmarca and see if that changes the EXPLAINs

7

Ok, so after adding the index on anuncios_auto.idmarca, the explains give me:


id	select_type	table	type	possible_keys	key		key_len		ref	rows	extra
1	SIMPLE		u       ALL     NULL            NULL            NULL		NULL	959	Using Where; Using temporary; Using filesort
1	SIMPLE		a       range	index_marca	index_marca	52		NULL    395	Using Where; Using join buffer



id	select_type		table		type	possible_keys	key		key_len		ref			rows	extra
1	PRIMARY			u               ALL     NULL            NULL		NULL		NULL                    959	Using Where; Using temporary; Using filesort
1	PRIMARY			a               range   index_marca	index_marca	52		NULL                    395	Using Where; Using join buffer
1	DEPENDENT SUBQUERY	anuncios_auto	ref     index_marca	index_marca	52		mpl_class.a.idmarca	53	Using Where; Using temporary; Using filesort
8

looks like an improvement… how about the performance of those queries, any better?

9

Hi r937,

Yep, it improved by 100%. It usually took 30-45 seconds (if it didn’t hang) and now it takes 0,022 seconds :smiley:

Thank you for your time and patience.