CODEWITHSUNDEEP
pythondatabasemysql-connector
wildcolor
wildcolor

Reputation: 574

python mysql.connector fetchall() run extremely slow

I have a simple sql query using python and mysql.connector

query = 'SELECT age, score FROM patient'
cursor.execute(query)
dr = cursor.fetchall() or cursor.fetchone()
print(dr)

In phpmyadmin, this query is very fast (0.003s). It also runs fast in Python if I use fecthone(). However, it becomes extremely slow if I use fetchall(). Sometimes it take a few minutes and prints result. Sometimes it simply failed and halt.

My data is about 440,000 lines. I want to actually plot score against age, so I have to read all data out. Someone can help me?

ps: if I get rid of fetchall(), fetchone() and print, it runs very fast. But then how do get my data and plot it?

Upvotes: 3

Views: 2207

Answers (1)

9000
9000

Reputation: 40884

Fetching all 440k records can legitimately be slow.

If you want to draw a plot, you usually don't need 440k points; your screen is likely only about 2000 to 4000 pixels wide, 100-200 times smaller. You can show parts of the plot fetching only relevant part of the data, or you can pre-compute a scaled-down version (IIRC MySQL does not have native table sampling support).

As a side note: if you care about database performance at all, routinely inspect query plans. A plan for fetching one record can be drastically different than a plan for fetching all of them, and they likely will have different priorities if your database serves many concurrent requests. This all affects data latency significantly.

Upvotes: 2

Related Questions