SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query time select join

    Hi all.

    I have two tables in MySQL:

    1) _tblA 9.767 rows, fields A_ID and A_NAME (Index, Full text)
    2) _tblB 10.287 rows, fields B_ID and B_NAME (Index, Full text)

    I execute this query JOIN:

    PHP Code:
    SELECT A.A_NAME
            
    B.B_NAME 
     FROM _tblA A
              JOIN _tblB B ON 
              B
    .B_NAME left(A_NAME,4)
     
    GROUP BY 
           B
    .B_NAME
     ORDER BY 
           B
    .B_NAME ASC 
    The output is 39 records and query time is 5.088 s.

    You can speed this query? 5 seconds is eternity time....

    Thanks in advance.

  2. #2
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    how many records are in your table?
    Ryan B | My Blog | Twitter

  3. #3
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rguy84 View Post
    how many records are in your table?
    In the _tblA I have 9.767 records
    In the _tblB I have 10.287 records

    The output of the query join is 39 records.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    The query is slow because you apply a function in the ON clause of the JOIN. Because the function is there MySQL cannot use any indexes and has to revert to a full tablescan every time.

    You could add a column to A, say A_NAME2, containing just the first 4 characters of A.A_NAME.
    It is completely redundant data, but that way you could rewrite the JOIN to B.B_NAME = A.A_NAME2

    if both B.B_NAME and A.A_NAME2 are indexed that should speed up the query considerably.

    It's a bit nasty because of the redundancy, but it works.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    The query is slow because you apply a function in the ON clause of the JOIN. Because the function is there MySQL cannot use any indexes and has to revert to a full tablescan every time.

    You could add a column to A, say A_NAME2, containing just the first 4 characters of A.A_NAME.
    It is completely redundant data, but that way you could rewrite the JOIN to B.B_NAME = A.A_NAME2

    if both B.B_NAME and A.A_NAME2 are indexed that should speed up the query considerably.

    It's a bit nasty because of the redundancy, but it works.
    B.B_NAME and A.A_NAME2 are indexed... I try your suggestions, thanks...

  6. #6
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT A.A_NAME,          
            
    B.B_NAME   
     FROM _tblA A           
          JOIN _tblB B 
          ON B
    .B_NAME A.A_NAME2
     GROUP BY         
          B
    .B_NAME  
     ORDER BY         
          B
    .B_NAME ASC 
    Query time 4.913 s
    B.B_NAME and A.A_NAME2 are indexed...


  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why do you need GROUP BY b.bname? you have a.a_name in the SELECT clause without an aggregate function on it, so you're going to return an indeterminate value for a.a_name

    why?

    also, could you do an EXPLAIN on your query please

    sometimes, when you require all the rows in the table, you're going to get a table scan anyway
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why do you need GROUP BY b.bname? you have a.a_name in the SELECT clause without an aggregate function on it, so you're going to return an indeterminate value for a.a_name

    why?
    Because in the _tblB, I have for example:

    Code:
    B_ID	B_NAME
    1	bill jordan
    ...
    32	bill jordan
    But I try GROUP BY A.A_NAME the query time is 4.928 s.

    also, could you do an EXPLAIN on your query please
    My tables:

    1) _tblA 9.767 rows, fields A_ID, A_NAME and A_NAME2 (Index, Full text)
    2) _tblB 10.287 rows, fields B_ID and B_NAME (Index, Full text)

    I need extract records when B.B_NAME = A.A_NAME2.
    The output now is 39 records.

    thanks.

  9. #9
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Could you post the SHOW CREATE TABLE for both those tables?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  10. #10
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Could you post the SHOW CREATE TABLE for both those tables?
    PHP Code:
    # MySQL-Front 5.0  (Build 1.183)

    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */
    ;
    /*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
    /*!40103 SET SQL_NOTES='ON' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;
    /*!40014 SET FOREIGN_KEY_CHECKS=0 */;


    # Host: localhost    Database: test
    # ------------------------------------------------------
    # Server version 5.0.45-community-nt

    #
    # Table Objects for table _tbla
    #

    DROP TABLE IF EXISTS `_tbla`;
    CREATE TABLE `_tbla` (
      `
    A_IDint(11NOT NULL,
      `
    A_NAMEvarchar(255) default NULL,
      `
    A_NAME2varchar(255) default NULL,
      
    PRIMARY KEY  (`A_ID`),
      
    KEY `A_NAME` (`A_NAME`),
      
    KEY `A_NAME2` (`A_NAME2`)
    ENGINE=InnoDB DEFAULT CHARSET=latin1;

    #
    # Dumping data for table _tbla
    #
    LOCK TABLES `_tblaWRITE;
    /*!40000 ALTER TABLE `_tbla` DISABLE KEYS */;

    INSERT INTO `_tblaVALUES (...);
    ...
    ...
    /*!40000 ALTER TABLE `_tbla` ENABLE KEYS */;
    UNLOCK TABLES;

    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */

    PHP Code:
    # MySQL-Front 5.0  (Build 1.183)

    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */
    ;
    /*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
    /*!40103 SET SQL_NOTES='ON' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;
    /*!40014 SET FOREIGN_KEY_CHECKS=0 */;


    # Host: localhost    Database: test
    # ------------------------------------------------------
    # Server version 5.0.45-community-nt

    #
    # Table Objects for table _tblb
    #

    DROP TABLE IF EXISTS `_tblb`;
    CREATE TABLE `_tblb` (
      `
    B_IDint(11NOT NULL default '0',
      `
    B_NAMEvarchar(255) default NULL,
      
    PRIMARY KEY  (`B_ID`),
      
    KEY `B_NAME` (`B_NAME`)
    ENGINE=InnoDB DEFAULT CHARSET=latin1;

    #
    # Dumping data for table _tblb
    #
    LOCK TABLES `_tblbWRITE;
    /*!40000 ALTER TABLE `_tblb` DISABLE KEYS */;

    INSERT INTO `_tblbVALUES (...);
    ...
    ...
    /*!40000 ALTER TABLE `_tblb` ENABLE KEYS */;
    UNLOCK TABLES;

    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */

  11. #11
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    If A.A_NAME is the same as the first 4 characters of B.B_NAME, why would you select both of them and GROUP BY B.B_NAME and not just SELECT DISTINCT B.B_NAME and forget about A.A_NAME altogether?
    Dropping that GROUP BY should speed up the query.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  12. #12
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    If A.A_NAME is the same as the first 4 characters of B.B_NAME, why would you select both of them and GROUP BY B.B_NAME and not just SELECT DISTINCT B.B_NAME and forget about A.A_NAME altogether?
    Dropping that GROUP BY should speed up the query.
    I'm not sure...

    PHP Code:
     SELECT DISTINCT A.A_NAME2,
                      
    B.B_NAME     
      FROM _tblA A                 
      JOIN _tblB B        
              ON B
    .B_NAME A.A_NAME2  
      ORDER BY  
              B
    .B_NAME ASC 
    Query time: 5.0822s...

  13. #13
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Don't select A.A_NAME2

    Just

    SELECT DISTINCT B.B_NAME
    FROM ....
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  14. #14
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I try this:

    PHP Code:
     SELECT DISTINCT B.B_NAME     
      FROM _tblA A                 
      JOIN _tblB B        
              ON B
    .B_NAME A.A_NAME2  
      ORDER BY  
              B
    .B_NAME ASC 
    Query time: 4.8641s...[/QUOTE]


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
  •