Query time select join

[viki1967]

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:

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.

[rguy84]

how many records are in your table?

[viki1967]

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.

[ScallioXTX]

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.

[viki1967]

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...

[viki1967]

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...

[r937]

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

[viki1967]

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:

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.

[ScallioXTX]

Could you post the SHOW CREATE TABLE for both those tables?

[viki1967]

Could you post the SHOW CREATE TABLE for both those tables?

# 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_ID` int(11) NOT NULL,
  `A_NAME` varchar(255) default NULL,
  `A_NAME2` varchar(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 `_tbla` WRITE;
/*!40000 ALTER TABLE `_tbla` DISABLE KEYS */;

INSERT INTO `_tbla` VALUES (...);
...
...
/*!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 */; 

# 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_ID` int(11) NOT NULL default '0',
  `B_NAME` varchar(255) default NULL,
  PRIMARY KEY  (`B_ID`),
  KEY `B_NAME` (`B_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `_tblb` VALUES (...);
...
...
/*!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 */; 

[ScallioXTX]

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.

[viki1967]

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...

 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...

[ScallioXTX]

Don't select A.A_NAME2

Just

SELECT DISTINCT B.B_NAME
FROM ....

[viki1967]

Ok, I try this:

 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]