SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MySQL Returning the top 5 of each make

    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:

    Code MySQL:
    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.
    Yours truely
    Mário Ramos

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could you plesae run the following two EXPLAINs --
    Code:
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Yours truely
    Mário Ramos

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    any chance you could format those results so that we can read them with the column headers over the data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Yours truely
    Mário Ramos

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mjpr View Post
    Hope it's more readable now.
    thank you for the effort, but it's a bit worse like that

    i was hoping for this --
    Code:
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, so after adding the index on anuncios_auto.idmarca, the explains give me:


    Code:
    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

    Code:
    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
    Yours truely
    Mário Ramos

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    looks like an improvement... how about the performance of those queries, any better?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

    Thank you for your time and patience.
    Yours truely
    Mário Ramos


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •