Hi all.
I have two tables in MySQL:
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.
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.
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…
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…
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
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:
I need extract records when B.B_NAME = A.A_NAME2.
The output now is 39 records.
thanks.
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 */;
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…
Don’t select A.A_NAME2
Just
SELECT DISTINCT B.B_NAME
FROM …
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]