2

I have the following simple join query

SELECT
  count(*) 
FROM 
  DBx.caseview p2015
  INNER JOIN DBy.caseview p2014 ON p2015.casenumber=p2014.casenumber;

For some reason it just leaves MySQL hanging there for a lot of time until I get tired and cancel it. On the contrary, if run exactly the same code on MSSQL with the same data set the query takes a few seconds at most.

Is there a parameter that needs to be changed on MySQL to speed up this type of queries?

Here's my table in MySQL

CREATE TABLE `caseview` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CASEID` varchar(18) DEFAULT NULL,
  `CASENUMBER` int(10) DEFAULT NULL,
  `ACCOUNTID` varchar(18) DEFAULT NULL,
  `ACCOUNT` varchar(256) DEFAULT NULL,
  `ASSETID` varchar(18) DEFAULT NULL,
  `SAPPRODUCTGROUP` varchar(10) DEFAULT NULL,
  `PRODUCT` varchar(128) DEFAULT NULL,
  `FAMILY` varchar(128) DEFAULT NULL,
  `CONTACTID` varchar(18) DEFAULT NULL,
  `OWNERID` varchar(18) DEFAULT NULL,
  `TYPE` varchar(128) DEFAULT NULL,
  `PRIORITY` varchar(24) DEFAULT NULL,
  `ORIGIN` varchar(24) DEFAULT NULL,
  `SUBJECT` varchar(256) DEFAULT NULL,
  `STATUS` varchar(24) DEFAULT NULL,
  `LASTACTIVITY` varchar(1024) DEFAULT NULL,
  `INITALDESCRIPTION` varchar(1024) DEFAULT NULL,
  `CLOSEDDATE` datetime DEFAULT NULL,
  `CREATEDDATE` datetime DEFAULT NULL,
  `LASTMODIFIEDDATE` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=122393 DEFAULT CHARSET=utf8;

There's two tables with exactly the same configuration as above, just different data

DBx.caseview p2015 has 197647 rows DBy.caseview p2014 has 122392 rows

pala_
  • 8,901
  • 1
  • 15
  • 32
efx
  • 63
  • 9
  • 2
    What indexes are in use? Also, you can use explain http://dev.mysql.com/doc/refman/5.7/en/explain.html to see what the query plan is. This will let you know what indexes are being used. – Rasika Mar 31 '15 at 04:46
  • 1
    Maybe use `COUNT(field_Id)` – sqluser Mar 31 '15 at 04:47
  • @sqluser it would be slower. – zerkms Mar 31 '15 at 04:50
  • 2
    @efx provide `EXPLAIN` + `SHOW CREATE TABLE` for both tables + How many rows in both tables + How many rows match. PS: it's not a "simple" query - it's a scan over the whole table (not obvious over which one though) – zerkms Mar 31 '15 at 04:50
  • I'm curious about the `FROM` and `JOIN` clauses. Are `tablex` and `tabley` separate databases? – Phil Mar 31 '15 at 05:08
  • Are you using SQL transaction in your code? – Lahiru Cooray Mar 31 '15 at 05:54
  • Is `casenumber` an index in either table? How many rows in each table? – Rick James Apr 07 '15 at 01:40
  • possible duplicate of ["SELECT COUNT(\*)" is slow, even with where clause](http://stackoverflow.com/questions/511820/select-count-is-slow-even-with-where-clause) – 1010 Apr 17 '15 at 01:59
  • @zerkms i agree is scanning the whole table and yes that's the issue b/c it doesn't have any column indexes. But this is not a problem on mssql but on mysql it is? – efx Apr 17 '15 at 01:59
  • add the indexes on casenumbers. that's what they're for. – pala_ Apr 17 '15 at 02:00
  • @Phil my mistake i meant DBx and DBy, different DBs same table name and config just different data set – efx Apr 17 '15 at 02:00

1 Answers1

0

Create an Index for CASENUMBER field.

ALTER TABLE `caseview` ADD INDEX ( `CASENUMBER` ) ;

Give it a few seconds to create the index and run the query again.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • well i just tried to add the index to my test environment it has been more than an hour and the process is still running, how long should it take? – efx Apr 17 '15 at 03:18
  • Well, it depends on how many records you have, your hardware, etc... But believe me, you need it. –  Apr 18 '15 at 00:42
  • well i do agree i need them, but still, i asked why mssql w/o them does the job in seconds and mysql can't? even with the indexes takes a considrable time in the same machine/hardware – efx Apr 18 '15 at 15:57
  • Is it possible for you to enable mysql slow queries log? Maybe it will turn out something. Also, mssql has been profiling queries since mssql 2005 (http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx) and it is possible that new versions could create internal indexes based on the sql it runs an the results he thinks you want. Also, you will need to add the index in that table in both databases. –  Apr 19 '15 at 13:29