'MySQL query from python (django app) taking very long to execute
I have 3 mysql (innodb) tables, 2 of which (called fineart_artworks and fineart_lots) have more than 1.6 million records each. The third table (called fineart_artists) has approx 570K records. I am creating the following view in order to extract some data from these 3 tables:
create or replace view featured_artists as
select fa.fa_artist_ID as artist_id,
fa.fa_artist_name as artist_name,
fal.fal_lot_sale_price_USD as artist_price_usd,
fa.fa_artist_name_prefix as prefix,
fa.fa_artist_nationality as nationality,
fa.fa_artist_birth_year as birthyear,
fa.fa_artist_death_year as deathyear,
fa.fa_artist_description as description,
fa.fa_artist_aka as aka,
fa.fa_artist_bio as bio,
fa.fa_artist_image as artistimage,
fa.fa_artist_genre as genre
from fineart_artists fa, fineart_artworks faa, fineart_lots fal
where fa.fa_artist_ID=faa.faa_artist_ID
and faa.faa_artwork_ID=fal.fal_artwork_ID;
This query takes about 12-15 secs to execute. After this, I am running the following query to actually get some data:
select artist_id, artist_name, sum(artist_price_usd) as price, prefix,
nationality, birthyear, deathyear, description, aka, bio,
artistimage, genre
from featured_artists
where artistimage is not NULL and artistimage != ''
group by artist_id
order by price desc;
This is taking about 5 minutes to execute. Sometimes even more than that. I am a python developer, and have a basic understanding of relational databases. However, I am not able to figure out why these queries are taking so long. I have worked with mysql databases before, and some of them were just as large, but it never took more than 30-40 secs to execute, even in resource starved systems (with 4GB memory). Any solution/workaround or pointer in the right direction would be greatly appreciated. Should you need more information, please just ask. I will provide you with whatever information you need.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
