CODEWITHSUNDEEP
mysql
Lhen
Lhen

Reputation: 181

Optimize current MySQL query of 2 tables

I have 2 tables, table1 and table2. Both have about 500000 records each. They have 2 similar columns, cid and year, both not unique (different year for each cid)

table1 columns: cid, year, status
tables2 columns: cid, year, acc, rej

When I make a query with a LIMIT 50, it already takes about half a minute to process. I'd like to optimize this query.

Here is my current query

SELECT table1.cid, acc, rej, table1.year 
FROM table1, table2 WHERE table1.status != 0 
AND table1.year = 2015 
AND table1.year = table2.year 
AND table1.cid = table.cid
LIMIT 50

Upvotes: 1

Views: 40

Answers (1)

sagi
sagi

Reputation: 40481

Add an index on table1 (cid,year) and table2 (cid,year) , it should speed up your query.

Also, please avoid the use of IMPLICIT join syntax(comma separated) and use the explicit syntax like this:

SELECT table1.cid, acc, rej, table1.year 
FROM table1
INNER JOIN table2
 ON(table1.year = table2.year and table1.cid = table.cid)
WHERE table1.status != 0 
 AND table1.year = 2015 
LIMIT 50

Upvotes: 2

Related Questions