Database indelen en up-to-date houden
Het beste kan je dit soort bulk inserts afhandelen met LOAD DATA INFILE, met andere woorden de query omzetten naar csv, in een bestand opslaan en dit inladen met:
Code (php)
1
2
3
2
3
LOAD DATA INFILE '/pad/naar/xml2csv.csv'
REPLACE INTO TABLE videos
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
REPLACE INTO TABLE videos
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Op 2 mil records ca 3 minuten.
Wat het date field betreft, het werkt, dus waarom niet gebruiken? Ik kan me vaag herinneren dat ik ooit wel gehad heb dat het niet kon, toen was de oplossing vrij simpel, veld naam tussen quotes en het ging wel. Wat toen met nu het verschil is geen idee, maar het werkt!
@- SanThe -:
De enkele quotes worden door $this->db->escape geregeld.
@Ger van Steenderen:
Zojuist een erg interessant artikel gelezen met daarbij een geweldig script: http://kvz.io/blog/2009/03/31/improve-mysql-insert-performance/
Ik ga daarmee even spelen. Ik zal dan uit het script wat ik tot nu toe heb alle query's weghalen en die vervangen door een array die opgebouwd wordt.
Daar zit namelijk nog wel wat logica in die nodig is. Ik kan niet simpelweg de XML omzetten naar een CSV en inladen. MySQL heeft geen idee in welk tabel wat moet natuurlijk. Ook het koppel tabel zal hij niets van begrijpen. Dus zal per tabel een array opbouwen en door de "mysqlBulk" functie gooien. Ik ben zeer benieuwd!
Update:
Lekker aan het spelen, tot nu toe al voor elkaar om 22278 items per seconden toe te voegen! Later meer!
Gewijzigd op 11/11/2012 17:17:45 door Roy -
Als iemand een categorie kiest kom je op de categorie pagina en dienen daar alle videos tevoorschijn te komen. Hier zit een pagination class in dus wil ook nog graag weten hoeveel videos er in die categorie zijn.
Hierbij moet het volgende gebeuren:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
//Het category_id selecteren bij de naam
SELECT category_id
FROM categories
WHERE category_name='Naam van de categorie'
//Alle video_id's selecteren in die categorie
SELECT video_id
FROM video_cat
WHERE cat_id='Category_id van de vorige query'
//Aantal gevonden als resultaat
//Gaan we daarna verder om ook de videos te krijgen:
//Loopen door alle eerder geselecteerde video_id's en een limit adhv de pagination
SELECT *
FROM videos
WHERE video_id='Video_id uit vorige query'
LIMIT 0,50
SELECT category_id
FROM categories
WHERE category_name='Naam van de categorie'
//Alle video_id's selecteren in die categorie
SELECT video_id
FROM video_cat
WHERE cat_id='Category_id van de vorige query'
//Aantal gevonden als resultaat
//Gaan we daarna verder om ook de videos te krijgen:
//Loopen door alle eerder geselecteerde video_id's en een limit adhv de pagination
SELECT *
FROM videos
WHERE video_id='Video_id uit vorige query'
LIMIT 0,50
Toevoeging:
Misschien beter de limit bij het selecteren uit de video_cat te gebruiken?
Weer wat verder :)
Query voor het tellen van de aanwezige videos in een categorie voor elkaar:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT COUNT(video_cat.video_id) AS amount
FROM video_cat
LEFT JOIN categories
ON video_cat.cat_id = categories.category_id
WHERE categories.category_name=".$this->db->escape($cat)."
FROM video_cat
LEFT JOIN categories
ON video_cat.cat_id = categories.category_id
WHERE categories.category_name=".$this->db->escape($cat)."
Nog even verder stoeien om de videos te krijgen uit een categorie!
Bingo!?
Lijkt erop dat ik ook die voor elkaar heb!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT videos.video_id AS videos_id, videos.title AS title
FROM videos
INNER JOIN
(
SELECT video_cat.video_id
FROM video_cat
LEFT JOIN categories
ON video_cat.cat_id = categories.category_id
WHERE categories.category_name=".$this->db->escape($cat)."
) AS vid_cat_match
ON videos.video_id = vid_cat_match.video_id
LIMIT ".$this->db->escape($offset).",".$this->db->escape($limit)."
FROM videos
INNER JOIN
(
SELECT video_cat.video_id
FROM video_cat
LEFT JOIN categories
ON video_cat.cat_id = categories.category_id
WHERE categories.category_name=".$this->db->escape($cat)."
) AS vid_cat_match
ON videos.video_id = vid_cat_match.video_id
LIMIT ".$this->db->escape($offset).",".$this->db->escape($limit)."
Graag een reactie of dit goed/netjes is gezien ik geen join expert ben :)
En nog verder! :)
Ik ben lekker aan het spelen met joins, moet zeggen dat het voor me steeds logischer en makkelijker begint te worden!
Zojuist op het volgende gekomen om de volgende array te krijgen:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
[supplier_id] => 1
[title] => Naam video
[duration] => 180
[date] => 2012-10-20
[categories] => Tech,Games
[tags] => COD,FPS
[title] => Naam video
[duration] => 180
[date] => 2012-10-20
[categories] => Tech,Games
[tags] => COD,FPS
Query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
supplier_id,
title,
duration,
date,
(
SELECT GROUP_CONCAT(category_name)
FROM categories
LEFT JOIN video_cat
ON video_cat.cat_id = categories.category_id
WHERE video_cat.video_id=".$this->db->escape($video_id)."
) AS categories,
(
SELECT GROUP_CONCAT(tag_name)
FROM tags
LEFT JOIN video_tag
ON video_tag.tag_id = tags.tag_id
WHERE video_tag.video_id=".$this->db->escape($video_id)."
) AS tags
FROM videos
WHERE videos.video_id=".$this->db->escape($video_id)."
supplier_id,
title,
duration,
date,
(
SELECT GROUP_CONCAT(category_name)
FROM categories
LEFT JOIN video_cat
ON video_cat.cat_id = categories.category_id
WHERE video_cat.video_id=".$this->db->escape($video_id)."
) AS categories,
(
SELECT GROUP_CONCAT(tag_name)
FROM tags
LEFT JOIN video_tag
ON video_tag.tag_id = tags.tag_id
WHERE video_tag.video_id=".$this->db->escape($video_id)."
) AS tags
FROM videos
WHERE videos.video_id=".$this->db->escape($video_id)."
Maar... ik noem het nog steeds spelen :) graag een reactie of deze en eerdere genoemde queries netjes zijn of dat dit wellicht beter/makkelijker kan.
Gewijzigd op 13/11/2012 13:24:17 door Roy -