0

I'm writing a query in mysql to join two tables. And both tables have more than 50,000 records.

Table EMP Columns empid,

project,

code,

Status

Table EMPINFO

empid,

project,

code,

projecttype,

timespent,

skills

In each table there is candidate key [empid, project, code]

So when I join the table using INNER join

like this INNER JOIN 
ON a.empid = b.empid 
and a.project = b.project
and a.code = b.code

I'm getting the result, but if I add count(*) in outer query to count number of records, it takes lot of time something connection gets failed.

Is there any way to speed up to get number of records ?

And I would like to hear more suggestions to speed up inner join query as well having same candidate key in both tables.

Redbeard011010
  • 954
  • 6
  • 20
roy
  • 101
  • 1
  • 3
  • 9
  • 4
    Please show your full query and the table definitions. – Renzo Dec 28 '15 at 14:51
  • Do you have indexes on the fields you're joining with? – ceejayoz Dec 28 '15 at 15:00
  • try [basic sql tuning](http://beginner-sql-tutorial.com/sql-query-tuning.htm) – saikumarm Dec 28 '15 at 15:00
  • Use EXPLAIN: http://dev.mysql.com/doc/refman/5.7/en/using-explain.html – Dan Dec 28 '15 at 15:04
  • Where is this count being used? Most record set objects in the UI have a record count automatically returned as part of the record set object. I guess I'm asking if you need the count in the Datababase or in the UI. – xQbert Dec 28 '15 at 15:06
  • **You need to show us the table and index definitions**, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We need row counts because that can affect query planning. If you know how to do an `EXPLAIN` or get an execution plan, put the results in the question as well. If you have no indexes, visit http://use-the-index-luke.com. – Andy Lester Dec 28 '15 at 15:17
  • @xQbert, I need count so that I could check how many records are returned. Because when I select number of records in my sql then upto 1000 to 2000 it shows records after running query. But when I extends that, its stops working, timed out. So I want to see how many records has been raised after applying join query mentioned above. – roy Dec 28 '15 at 15:36
  • Could it be something as simple as wrapping your query in a query `Select count(*) as totalCount, A.* from (Your query here) A` or a prior stack answer: http://stackoverflow.com/questions/5060366/mysql-fastest-way-to-count-number-of-rows – xQbert Dec 28 '15 at 15:38
  • @xQbert, what makes the difference writing like you and I did. My query is like Select count(*) from (query) as A. Does it increase the speed ? – roy Dec 28 '15 at 15:40
  • I was returning the count on every row is all. if The max records you could have returned is the maximum of either table. So if both tables have < 60,000 records then record count should be < 60,000 If your db engine is timing out on 60,000 join then the other comments here make sense, you're missing join criteria, indexes, or have a poorly structured table. This seems like an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) so I'm going to back out. We seem to be trying to fix a count problem, but that's not really what you're after. – xQbert Dec 28 '15 at 15:43
  • @xQbert, In both tables there are 55,000 records each. You said indexes, is that possible to create indexes while making select query with inner join ? SO that I could number of records !! FAST :) – roy Dec 28 '15 at 15:52
  • No, indexes are added in DDL (data definition language) not DML (Data Markup Language) more on those [here](http://stackoverflow.com/questions/2578194/what-is-ddl-and-dml) Indexes have to exist prior to the DML execution to use them. as they have to be generated which is what gives you the speed boost. This is why @dan08 or renzo is asking for more detail in first few comments. – xQbert Dec 28 '15 at 15:55
  • @xQbert, So that's why am here friend. I've mentioned my factual information above and needed some catalyst to speed up the number of counts. In sql lots of tricks get applied and everyone has their own and I have none !! – roy Dec 28 '15 at 15:59
  • @xQbert, I got the records in two tables using ETL – roy Dec 28 '15 at 16:01
  • ETL (extract Transform and load) means you likely have no indexes or relationships defined. You need to add indexes to both tables to speed things up. LIkely on the joined columns; otherwise the joins are performed via full table scans which you can see is slow. – xQbert Dec 28 '15 at 16:02
  • @xQbert, SO I have to apply indexes all column of superkey columns in both tables ? – roy Dec 28 '15 at 16:12

1 Answers1

0
INDEX(empid, project, code) -- in any order.

Are these tables 1:1? If so, why do the JOIN in order to do the COUNT?

Please provide SHOW CREATE TABLE. (If there are datatype differences, this could be a big problem.)

Please provide the actual SELECT.

How much RAM do you have? Please provide SHOW VARIABLES LIKE '%buffer%';.

Rick James
  • 135,179
  • 13
  • 127
  • 222