Optimizing MySQL Queries
I am currently working on a website that needs some optimizations ... since the first page takes about 15-20 seconds to load, I thought some optimization would be nice.
Here is one query that showed up in the MySQL slow query log:
SELECT a.user,a.id
FROM `profil_perso` pp
INNER JOIN `acces` a ON pp.parrain = a.id
INNER JOIN `acces` ap ON ap.id = pp.id
WHERE pp.parrain_visibilite = '1'
AND a.actif = 1
GROUP BY a.id
ORDER BY ap.depuis DESC LIMIT 15;
In profil_perso
(lines ~ 207K - contains emails and profiles) there is perso_id
which is the primary key, there is also id
(foreign key) + parrain
(referer) + parrain_visibilite
(referent shown) which are indices.
In acces
there id
, which is the primary key, there is also depuis (registration date), which is indexed
The test actually shows this:
First time: 1.94532990456
Last time: 1.94532990456
Average duration: 0.0389438009262
I tried to put it this way:
SELECT DISTINCT a.id, a.user
FROM `profil_perso` pp
LEFT JOIN `acces` a ON pp.parrain = a.id
WHERE pp.parrain_visibilite = 1
AND a.actif = 1
AND pp.id != 0
ORDER BY pp.id DESC LIMIT 15;
Nevertheless, the benchmark shows this:
First time: 1.96376991272
Last time: 1.96376991272
Average time: 0.0393264245987
Any hint of reduced request time?
Here's the complete indices:
acces:
id (primary)
derniere_visite -- last visit
pays_id -- country_id
depuis -- registration time
perso_id -- foreign key to profil_perso primary key
actif -- account status
compte_premium -- if account is premium
profil_perso:
perso_id (primary)
id -- foreign key to acces primary key
genre -- gender
parrain_visibilite -- visibility of referer
parrain -- referer
parrain_contexte
telephone
orientation
naissance -- birthdate
photo -- if it has a picture
a source to share
Why do you have two JOIN
here?
Create a composite index on acces (actif, depuis)
:
CREATE INDEX ix_acces_actif_depuis ON acces (actif, depuis)
create a composite index on profil_perso (parrain, parrain_visibilite)
:
CREATE INDEX ix_profilperso_parrain_parrainvisibilite ON profil_perso (parrain, parrain_visibilite)
and try this:
SELECT a.user, a.id
FROM acces a
JOIN profil_perso p
ON pp.parrain = a.id
AND pp.parrain_visibilite = 1
WHERE a.actif = 1
ORDER BY
a.actif DESC, a.depuis DESC
LIMIT 15
This query will use index on actif
to avoid sorting, and index on profil_perso
to find and filter invisible ones parrain
.
Since you have LIMIT 15
, this request should be instant.
It will also help you know how selective your field is actif
.
To figure it out, run:
SELECT COUNT(DISTINCT actif) / COUNT(*)
FROM acces
a source to share
Native ansperters (s) depend on the distribution of the data (number of records, cardinality of fields and combination of fields, etc.) and schema, as in the query expression. Even with this information, we could only provide test suggestions, which would only lead to additional test suggestions.
But we could start with the first cut using the table schema as well as the results of the current EXPLAIN (run twiz, second and first result).
a source to share
Generally speaking, you need to make sure that your indexes are set up correctly - not only for primary keys, but also for foreign keys used in the table.
Also, it is usually preferable to have specific indices for whatever field you filter / order - so, again, make sure they are set accordingly.
However, I think the big performance here might be due to the fact that you are sorting 207k records to get the last 15 inserted - can you achieve the same in a different way?
a source to share