Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    14

    Unanswered: Problem ordering with join

    The schema:
    Code:
    Tables
    noticias n
    noticia_id
    noticia_categoria_id
    other_fields
    
    noticias_votacion nv
    id
    noticia_id
    other_fields
    
    noticias_categorias nc
    categoria_id
    other_fields
    
    Relations
    n.noticia_id -> nv.noticia_id [1-N]
    n.noticia_categoria_id -> nc.categoria_id [1-N]
    I need ordering the noticias' table by the number of rows(votes) in noticias_votacion, filtering by the categorie_id. I made a query, but it returns only the noticias that has at least a vote (a row in noticias_votacion).
    Code:
    SELECT n.*, COUNT(*) AS cnt 
    FROM noticias n, noticias_votacion nv, noticias_categorias nc 
    WHERE n.noticia_categoria_id = nc.categoria_id AND n.noticia_id = nv.noticia_id AND nc.categoria_id = 'alimentacion-bebidas'
    GROUP BY n.noticia_id
    How can I get noticias with no votes (no rows in noticias_votacion) ?

    Thank you in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT n.*
         , COUNT(nv.noticia_id) AS cnt 
      FROM noticias_categorias nc
    INNER
      JOIN noticias n
        ON n.noticia_categoria_id = nc.categoria_id
    LEFT OUTER
      JOIN noticias_votacion nv
        ON nv.noticia_id  = n.noticia_id 
     WHERE nc.categoria_id = 'alimentacion-bebidas'
    GROUP 
        BY n.noticia_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    14
    Thank you r937! That query works great.

    Now, almost the same query, other (similar) tables:
    Code:
    SELECT p.*, COUNT(pv.page_id) AS cnt 
    FROM pages_categories pc INNER JOIN pages p ON p.page_categorie_id = pc.categorie_id 
    LEFT OUTER JOIN pages_votacion pv ON pv.page_id = p.page_id 
    WHERE p.page_categorie_id IN ('belleza','belleza-para','cuidado-piel','ejercicios-faciales','cosmetica-natural','kohol','mascarillas-caseras','miswak-siwak','pintalabios-artesanales','las-arrugas-tratamientos','trucos-de-belleza','trucos-belleza-corporal','aceites-para-masajes','trucos-belleza-banos','trucos-celulitis','cremas-y-lociones','trucos-belleza-cuello','desodorantes-y-talcos','trucos-estrias','exfoliantes-corporales','flacidez','trucos-belleza-jabones','trucos-belleza-manos','piernas-y-pies','trucos-belleza-senos','trucos-belleza-facial','trucos-acne','trucos-antiarrugas','cejas-y-pestanas','trucos-dientes','exfoliantes-faciales','trucos-labios','manchas-en-la-piel','maquillaje','ojeras-y-bolsas','trucos-ojos','patas-de-gallo','trucos-piel-grasa','trucos-piel-normal','trucos-piel-seca','trucos-puntos-negros','trucos-belleza-cabello','trucos-cabellos-con-caspa','trucos-cabellos-debiles','trucos-cabellos-grasos','cabellos-secos','caida-del-cabello','lociones-y-enjuagues','peinados-paso-a-paso') GROUP BY p.page_id
    The problem is that pages has 8k rows, pages_votacion has 250k rows, and when I executed that query, the server crashed :P
    I remove pages_categories from the query and happened the same.

    So, Is there a way of doing the same, with a better performance ?
    Thank you again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    14
    Here are the tables schemas:
    Code:
    -- phpMyAdmin SQL Dump
    
    CREATE TABLE `pages` (
      `page_id` varchar(128) NOT NULL,
      `page_categorie_id` varchar(25) NOT NULL DEFAULT '',
      `page_author_id` varchar(20) NOT NULL DEFAULT '0',
      `page_newsletter_id` int(11) DEFAULT NULL,
      `page_title` varchar(255) NOT NULL DEFAULT '',
      `page_description` text NOT NULL,
      `page_image_galeria` int(11) NOT NULL DEFAULT '0',
      `page_active` int(1) NOT NULL DEFAULT '0',
      `page_text` text,
      `page_counter` int(10) NOT NULL DEFAULT '0',
      `page_comment_counter` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`page_id`),
      KEY `pid` (`page_id`),
      KEY `cid` (`page_categorie_id`),
      KEY `page_categorie_id` (`page_categorie_id`),
      KEY `page_author_id` (`page_author_id`),
      KEY `page_id` (`page_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0;
    
    CREATE TABLE `pages_categories` (
      `categorie_id` varchar(25) NOT NULL DEFAULT '',
      `categorie_title` varchar(255) NOT NULL DEFAULT '',
      `categorie_id_madre` varchar(25) NOT NULL DEFAULT '',
      PRIMARY KEY (`categorie_id`),
      KEY `cid` (`categorie_id`),
      KEY `categorie_id_madre` (`categorie_id_madre`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    CREATE TABLE `pages_votacion` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `page_id` varchar(128) NOT NULL,
      `rating` int(2) NOT NULL,
      `timestamp` varchar(16) NOT NULL,
      `ip` varchar(16) NOT NULL,
      `locId` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=249881 ;
    Thank you for your help

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do this first --
    Code:
    ALTER TABLE pages_votacion 
    ADD INDEX ( page_id )
    and then run this query --
    Code:
    SELECT p.*
         , COUNT(pv.page_id) AS cnt 
      FROM pages p 
    LEFT OUTER 
      JOIN pages_votacion pv 
        ON pv.page_id = p.page_id 
     WHERE p.page_categorie_id IN 
           ('belleza','belleza-para','cuidado-piel','ejercicios-faciales'
           ,'cosmetica-natural','kohol','mascarillas-caseras','miswak-siwak'
           ,'pintalabios-artesanales','las-arrugas-tratamientos'
           ,'trucos-de-belleza','trucos-belleza-corporal','aceites-para-masajes'
           ,'trucos-belleza-banos','trucos-celulitis','cremas-y-lociones'
           ,'trucos-belleza-cuello','desodorantes-y-talcos','trucos-estrias'
           ,'exfoliantes-corporales','flacidez','trucos-belleza-jabones'
           ,'trucos-belleza-manos','piernas-y-pies','trucos-belleza-senos'
           ,'trucos-belleza-facial','trucos-acne','trucos-antiarrugas'
           ,'cejas-y-pestanas','trucos-dientes','exfoliantes-faciales'
           ,'trucos-labios','manchas-en-la-piel','maquillaje','ojeras-y-bolsas'
           ,'trucos-ojos','patas-de-gallo','trucos-piel-grasa','trucos-piel-normal'
           ,'trucos-piel-seca','trucos-puntos-negros','trucos-belleza-cabello'
           ,'trucos-cabellos-con-caspa','trucos-cabellos-debiles'
           ,'trucos-cabellos-grasos','cabellos-secos','caida-del-cabello'
           ,'lociones-y-enjuagues','peinados-paso-a-paso') 
    GROUP 
        BY p.page_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2011
    Posts
    14
    Holy s***
    I won't understimate indexes any more. This is mind-opening.

    Thank you a lot!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    f.y.i. half your indexes are redundant (no effect on SELECTs but INSERTs and DELETEs are slower)

    PRIMARY KEY (`page_id`),
    KEY `pid` (`page_id`), -- redundant (see primary key)
    KEY `cid` (`page_categorie_id`),
    KEY `page_categorie_id` (`page_categorie_id`), -- redundant (see `cid`)
    KEY `page_author_id` (`page_author_id`),
    KEY `page_id` (`page_id`) -- redundant (see primary key)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2011
    Posts
    14
    I saw that and delete them.
    Thank you

  10. #10
    Join Date
    Oct 2011
    Posts
    1
    I have had the same problem. Thank you helping me to solve it

Posting Permissions

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