CODEWITHSUNDEEP
mysqlmysql-workbench
Extria
Extria

Reputation: 373

Optimizing MysQL query,

I have simple MySQL query which runs really slowly.

INSERT INTO People_by_County (City, County, State, score, Month_, person_id)
SELECT people.City, people.County, people.State, PPL_month.score, PPL_month.Month_, PPL_month.person_id
FROM PPL_month
INNER JOIN people ON PPL_month.person_id = people.person_id

Where is 700K rows in people table, and 2,9Mln rows in PPL_month table. The thing is that, before I ran similar query on different tables, and on 1 table there was 700K rows on another 400Mln and query was finished in 6h. And this one is running almost for 24h. Any ideas why it's so slow ? No other queries are running at the time, so no one is using up RAM.

Bellow you can see Explain of the query.

enter image description here

Upvotes: 0

Views: 69

Answers (1)

Sagar Gangwal
Sagar Gangwal

Reputation: 7980

First create INDEXon PERSON table as well as PPL_Month table.Then try to Execute this query

SELECT people.City, people.County, people.State, PPL_month.score, PPL_month.Month_, PPL_month.person_id
FROM PPL_month
INNER JOIN people ON PPL_month.person_id = people.person_id

How much time it took for execution?Note down that timing and Note down Execution timing for same query without creating index on both table. You will definitely get more timing. So indirectly it indicates that Data you want to insert is more dependent on how fast it was fetched. So once Fetching is fast obviously insertion is faster than previous one.

Hope this will helps.

Upvotes: 1

Related Questions