Indexen
•Herberekenen bij insert/update/...
•Moeten in-mem zitten om efficiënt te zijn
•Teveel is ook niet goed
Thursday 19 January 2012
Slide 48
Slide 48 text
Limieten
•MySQL gebruikt maximaal één index per
tabel
Thursday 19 January 2012
Slide 49
Slide 49 text
select *
from comments
where visible = 1
and post_id = 23;
Thursday 19 January 2012
Slide 50
Slide 50 text
+-------------+----------+---------------+------+-------+-------------+
| select_type | table | possible_keys | key | rows | Extra |
+-------------+----------+---------------+------+-------+-------------+
| SIMPLE | comments | NULL | NULL | 10180 | Using where |
+-------------+----------+---------------+------+-------+-------------+
Thursday 19 January 2012
Slide 51
Slide 51 text
Thursday 19 January 2012
Slide 52
Slide 52 text
Meerdere kolommen
•mysql gebruikt maar één index per tabel
•index maken over meerdere kolommen
Thursday 19 January 2012
Slide 53
Slide 53 text
Joins explainen
•Nuttig
•MySQL beslissingen onderzoeken
Thursday 19 January 2012
Slide 54
Slide 54 text
select * from tags
inner join posts_tags on posts_tags.tag_id = tags.id
inner join posts on posts.id = posts_tags.post_id
inner join comments on comments.post_id = posts.id
where posts.author_id = 23
and posts.category_id in (23,5,18)
and comments.visible >= 1
and posts.visible >= 1;
Thursday 19 January 2012
Slide 55
Slide 55 text
select * from tags
inner join posts_tags on posts_tags.tag_id = tags.id
inner join posts on posts.id = posts_tags.post_id
inner join comments on comments.post_id = posts.id
where posts.author_id = 23
and posts.category_id in (23,5,18)
and comments.visible >= 1
and posts.visible >= 1;
Thursday 19 January 2012
Slide 56
Slide 56 text
select * from tags
inner join posts_tags on posts_tags.tag_id = tags.id
inner join posts on posts.id = posts_tags.post_id
inner join comments on comments.post_id = posts.id
where posts.author_id = 23
and posts.category_id in (23,5,18)
and comments.visible >= 1
and posts.visible >= 1;
Thursday 19 January 2012
Slide 57
Slide 57 text
select * from tags
inner join posts_tags on posts_tags.tag_id = tags.id
inner join posts on posts.id = posts_tags.post_id
inner join comments on comments.post_id = posts.id
where posts.author_id = 23
and posts.category_id in (23,5,18)
and comments.visible >= 1
and posts.visible >= 1;
Thursday 19 January 2012
Slide 58
Slide 58 text
String-zoeken
•Titel die begint met “van” (“van%”)
•Titel die eindigt met “van” (“%van”)
•Titel die “van” bevat (“%van%”)
Thursday 19 January 2012
Slide 59
Slide 59 text
Bewerkingen
•Een bewerking op een variabel veld
•Bewerking omkeren
Thursday 19 January 2012
Slide 60
Slide 60 text
Cases
Thursday 19 January 2012
Slide 61
Slide 61 text
select *
from posts
where category like ‘%,52,%’
or category like ’52,%’
or category like ‘%,52’;
Thursday 19 January 2012
Slide 62
Slide 62 text
NOW()‘);
...
Thursday 19 January 2012
Slide 63
Slide 63 text
select *
from posts
order by rand()
limit 5;
Thursday 19 January 2012
Slide 64
Slide 64 text
select max(id) from posts;
...
... php-code
...
select * from posts
where id in (34,65,78,123,453);
Thursday 19 January 2012
Slide 65
Slide 65 text
select *
from posts
where title like ‘%wraak%’;
Thursday 19 January 2012
Slide 66
Slide 66 text
Zoeken
•Fulltext index
•Gespecialiseerde software
•Lucene / sphinx / solr
Thursday 19 January 2012
Slide 67
Slide 67 text
subselects
•Op te lossen via een join
•Join te optimaliseren met indexen
Thursday 19 January 2012
Slide 68
Slide 68 text
charset trouble
•charsets zijn overal (connectie, db,
schema, ...)
•export / import meeste probleem
•collations (duitse SS!)
Thursday 19 January 2012