Datensätze in Query per Mysql zählen?

bofh1337

bofh1337

Erfahrenes Mitglied
Ich habe in einer Tabelle 350.000 Datensätze, diese will ich der Reihe nach in 100er Pakete auslesen.
Bis jetzt habe ich es mit Limit und Offset gemacht, muss aber merken, dass das ganze immer langsamer wird, je höher das Offset ist.

Gibt es eine Möglichkeit, per Query die Datensätze aus der DB zu holen und gleichzeitig zu zählen?

zb. so lange die Daten aus der DB holen, bis ein Zähler, welcher von Mysql hochgezählt wird, bei zb. 100 steht?
 
Moin bofh1337,

ja, es gibt auch in MySQL stored Procedures und for-next-Anweisungen, die jeweils 100 Datensätze durchfetchen können...

Aber das Ganze sollte doch schon irgendeinen Sinn haben.

Ich meine, dein lapidares Der-Reihe-nach macht ohne Sortierung keinen Sinn und für ein einfaches Zähl-mir-die-Datensätze-in-einem-Hunderterblock brauchst du auch keine StP.

Was ist denn der Plan bzw. der Anwendungsfall?

Grüße
Biber
 
Biber3 hat gesagt.:
Moin bofh1337,

ja, es gibt auch in MySQL stored Procedures und for-next-Anweisungen, die jeweils 100 Datensätze durchfetchen können...

Aber das Ganze sollte doch schon irgendeinen Sinn haben.

Ich meine, dein lapidares Der-Reihe-nach macht ohne Sortierung keinen Sinn und für ein einfaches Zähl-mir-die-Datensätze-in-einem-Hunderterblock brauchst du auch keine StP.

Was ist denn der Plan bzw. der Anwendungsfall?

Grüße
Biber
Zum Vergrößern anklicken....

Der Plan ist das Konvertieren einer alten (dicken) Vbulletin-Datenbank mit ~7000 User, ~70000 Themen und rund 400000 Beiträgen ins Wordpress (ein Plugin-Forum)

Das ganze Script läuft bereits, ich merke aber, das es immer langsamer läuft, je länger es läuft.
In einem Test habe ich dann feststellen müssen, dass ein Nutzen des Offsets in meinem Fall ein extremer Performance-Killer ist.

Ein sortieren der Datensätze kann man ja noch einbauen, das sollte kein Problem sein.
 
Hi,

wenn du eine Sortierung an Hand eines Index einbaust, dann sollte das ganze selbst mit LIMIT ziemlich schnell gehen. Ich vermute, dass er ohne Index halt immer wieder einen FTS (Full-Table-Scan) machen muss, was dann dementsprechend immer länger dauert.

Du kannst dir ja den Query-Plan einmal mit "EXPLAIN SELECT ...." anzeigen lassen.

Grüsse,
BK

Grüsse,
BK
 
Ein Explain sagt mir genau das, was ich schon am Anfang befürchtet habe,- der einzige Index ist die "usergroupid", aber nicht die "userid" worauf es eigentlich ankommt.

Das wäre die Query:

Code: 
        $query->select('a.userid AS user_id,
            a.usergroupid AS user_group_id,
            a.username AS user_name,
            a.password AS user_pass,
            a.email AS user_mail,
            a.homepage AS user_homepage,
            a.icq AS user_icq,
            a.aim AS user_aim,
            a.yahoo AS user_yahoo,
            DATE_FORMAT(FROM_UNIXTIME(a.joindate), "%Y-%m-%d %H:%m:%s") AS user_register,
            DATE_FORMAT(FROM_UNIXTIME(a.lastvisit), "%Y-%m-%d %H:%m:%s") AS user_last_login,
            DATE_FORMAT(FROM_UNIXTIME(a.lastactivity), "%Y-%m-%d %H:%m:%s") AS user_last_activity,
            DATE_FORMAT(FROM_UNIXTIME(a.lastpost), "%Y-%m-%d %H:%m:%s") AS user_last_post,
            a.posts AS user_posts,
            a.birthday_search AS user_birthday,
            b.title AS user_title')
            ->from($this->db->quoteName('#__user') . ' AS a')
            ->leftJoin($this->db->quoteName('#__usergroup') . ' AS b USING (usergroupid)');

Wobei... das "user_title" aus der 2. Tabelle kann ich eigentlich weg lassen...
 

Anhänge

  • explain.png
    explain.png
    15,2 KB · Aufrufe: 8
Hi,

kannst du nicht temporär einen Index auf die user_id anlegen? Danach noch ein "ORDER BY a.userid" rein und schon sollte es schneller laufen.

Grüsse,
BK
 
Na ja,

bis hierhin kann es ja bei 7000 Usern keine Probleme geben - mit ohne ohne Index, mit oder ohne LEFT JOIN auf Tabelle b.
Bei den paar Usern wird mySQL auch bei vorhandenem Index nicht lange rumfackeln: es werden alle Felder (nicht nur ein Indexfeld) gebraucht und alle Datensätze, also wird es ein FTS bleiben. Was soll's, einmaliges Senken der Lesekopfes auf die Festplatte, 2 Sektoren in den Hauptspeicher gewummt und gut.

Interessant wird es doch erst danach - werden jetzt die Child-Sätze aller UserIDs nacheinander abklappert, also erst alle Beiträge/Kommentare von User #1 migriert, danach alle von User #2, User #3 ...bis User #7000?
Oder werden alle kommentare/Beiträge stumpf in der Reihenfolge ihrer physikalischen Speicherung gelesen/migriert, nachdem alle User-Stammdaten verarbeitet wurden?

Und wo sind jetzt die erwähnten 100er-Häppchen, die mit LIMIT und OFFSET erzeugt werden?

Bei diesen Folgeschritten wird es doch erst relevant, ob dann ein Index vorliegt, der z.B. alle Kommentare vom User #1 oder User#4711 mit einem INDEX RANGE zielsicher greifen kann.

@bofh1337: Wie geht es denn weiter, nachdem die User-Stammdaten gelesen wurden?

Grüße
Biber
 
Stimmt, da habe ich mich auch geirrt, die User sind nicht das Problem, sondern die Beiträge (rund 400.000 Stück).

Die Querys sind komplett so aufgebaut:
Für die User:
Code: 
    public function getUsers($start_user, $get_as_numrows = false)
    {
        $query = $this->db->getQuery(true);

        if ($get_as_numrows === true)
        {
            $query->select('COUNT(userid) AS counted')
                ->from($this->db->quoteName('#__user'));
            $this->db->setQuery($query);
        }
        else
        {
            $query->select('a.userid AS user_id,
                a.usergroupid AS user_group_id,
                a.username AS user_name,
                a.password AS user_pass,
                a.email AS user_mail,
                a.homepage AS user_homepage,
                a.icq AS user_icq,
                a.aim AS user_aim,
                a.yahoo AS user_yahoo,
                DATE_FORMAT(FROM_UNIXTIME(a.joindate), "%Y-%m-%d %H:%m:%s") AS user_register,
                DATE_FORMAT(FROM_UNIXTIME(a.lastvisit), "%Y-%m-%d %H:%m:%s") AS user_last_login,
                DATE_FORMAT(FROM_UNIXTIME(a.lastactivity), "%Y-%m-%d %H:%m:%s") AS user_last_activity,
                DATE_FORMAT(FROM_UNIXTIME(a.lastpost), "%Y-%m-%d %H:%m:%s") AS user_last_post,
                a.posts AS user_posts,
                a.birthday_search AS user_birthday,
                b.title AS user_title')
            ->from($this->db->quoteName('#__user') . ' AS a')
            ->leftJoin($this->db->quoteName('#__usergroup') . ' AS b USING (usergroupid)');
            $this->db->setQuery($query, $start_user, $this->rows_at_once);
        }

        $this->db->execute();

        // cleanup
        unset($start_user, $query);

        if ($get_as_numrows === true)
        {
            $row = $this->db->loadAssoc();
            return $row['counted'];
        }
        return $this->db->loadAssocList();
    }

für die Foren:
Code: 
    public function getForum($start_forum, $get_as_numrows = false)
    {
        $query = $this->db->getQuery(true);

        if ($get_as_numrows === true)
        {
            $query->select('COUNT(forumid) AS counted')
                ->from($this->db->quoteName('#__forum'));
            $this->db->setQuery($query);
        }
        else
        {
            $query->select('f.forumid AS forum_id,'
                . 'f.title AS forum_title,'
                . 'f.description AS forum_description,'
                . 'f.displayorder AS forum_display_order,'
                . 'f.replycount AS forum_reply_count,'
                . 'DATE_FORMAT(FROM_UNIXTIME(f.lastpost), "%Y-%m-%d %H:%m:%s") AS forum_last_post_datetime,'
                . 'f.lastposter AS forum_last_post_user_name,'
                . 'f.lastthreadid  AS forum_last_thread_id,'
                . 'f.threadcount AS forum_thread_count,'
                . 'f.parentid AS forum_parent_id,'
                . 'f.lastpostid AS forum_last_post_id,'
                . 'u.userid AS forum_last_post_user_id')
            ->from($this->db->quoteName('#__forum') . ' AS f')
            ->join('LEFT', $this->db->quoteName('#__user') . ' AS u ON (f.lastposter = u.username)')
            ->order($this->db->quoteName('parentid') . ' ASC');

            $this->db->setQuery($query, $start_forum, $this->rows_at_once);
        }

        $this->db->execute();

        // cleanup
        unset($start_forum, $query);

        if ($get_as_numrows === true)
        {
            $row = $this->db->loadAssoc();
            return $row['counted'];
        }

        return $this->db->loadAssocList();
    }


Für die Threads:
Code: 
    public function getThreads($start_thread, $get_as_numrows = false)
    {
        $query = $this->db->getQuery(true);

        if ($get_as_numrows === true)
        {
            $query->select('COUNT(threadid) AS counted')
                ->from($this->db->quoteName('#__thread'));
            $this->db->setQuery($query);
        }
        else
        {
            $query->select('t.threadid AS thread_id,'
                . ' t.firstpostid AS first_post_id,'
                . ' t.title AS thread_title,'
                . ' t.forumid AS forum_id,'
                . ' t.open AS thread_status,'
                . ' t.replycount AS thread_posts,'
                . ' t.postuserid AS user_id_post,'
                . ' DATE_FORMAT(FROM_UNIXTIME(t.dateline), "%Y-%m-%d %H:%m:%s") AS datetime_thread,'
                . ' t.views AS thread_views,'
                . ' DATE_FORMAT(FROM_UNIXTIME(t.lastpost), "%Y-%m-%d %H:%m:%s") AS datetime_last_post,'
                . ' t.lastpostid AS last_post_id,'
                . ' p.parentid AS thread_parent_id,'
                . ' p.pagetext AS thread_text,'
                . ' p.ipaddress AS thread_ip_address')
            ->from($this->db->quoteName('#__thread') . ' AS t')
            ->join('INNER', $this->db->quoteName('#__post') . ' AS p USING (threadid)')
            ->where('p.parentid = ' . $this->db->quote(0));
            $this->db->setQuery($query, $start_thread, $this->rows_at_once);
        }

        $this->db->execute();

        // cleanup
        unset($start_thread, $query);

        if ($get_as_numrows === true)
        {
            $row = $this->db->loadAssoc();
            return $row['counted'];
        }
        // cleanup
        unset($get_as_numrows);

        return $this->db->loadAssocList();
    }


Und die Beiträge (hier ist es etwas ölänger, weil das VB-Forum jeweils eine History anlegt, wenn ein Beitrag bearbeitet wird. Der letzte Beitrags-Text steht also nicht in der "vb_post"-Tabelle, sondern in der "vb_postedithistory"... und da brauche ich dann jeweils nur den letzten Eintrag (falls für den aktuellen Beitrag eine History vorhanden ist):
Code: 
    public function getPosts($start_post, $get_as_numrows = false)
    {
        $query = $this->db->getQuery(true);

        if ($get_as_numrows === true)
        {
            $query->select('COUNT(postid) AS counted')
                ->from($this->db->quoteName('#__post'));
            $this->db->setQuery($query);
        }
        else
        {
            $query->select('p.postid AS post_id, p.threadid AS thread_id, p.parentid AS post_parent_id, p.userid AS user_id,'
                . ' p.title AS post_title, DATE_FORMAT(FROM_UNIXTIME(p.dateline), "%Y-%m-%d %H:%m:%s") AS post_datetime,'
                . ' p.pagetext AS post_text, p.ipaddress AS user_ip, t.firstpostid AS first_post_id')
                ->from($this->db->quoteName('#__post') . ' AS p')
                ->leftjoin($this->db->quoteName('#__thread') . ' AS t USING (threadid)')
                ->where('p.parentid <> 0');
            $this->db->setQuery($query, $start_post, $this->rows_at_once);
        }

        $this->db->execute();

        if ($get_as_numrows === true)
        {
            $row = $this->db->loadAssoc();
            return $row['counted'];
        }
        else
        {

            $rows_tmp = $this->db->loadAssocList();
            $rows = array();
            $i = 0;
            foreach ($rows_tmp as $row)
            {
                $query1 = $this->db->getQuery(true);

                $query1->select($this->db->quoteName('title') . ' AS post_title, DATE_FORMAT(FROM_UNIXTIME(' . $this->db->quoteName('dateline') . '), "%Y-%m-%d %H:%m:%s") AS post_datetime,'
                    . '' . $this->db->quoteName('pagetext') . ' AS post_text')
                    ->from($this->db->quoteName('#__postedithistory'))
                    ->where($this->db->quoteName('postid') . ' = ' . $this->db->quote($row['post_id']) . ' AND (' . $this->db->quoteName('userid') . ' = ' . $this->db->quote($row['user_id']) . ')')
                    ->order($this->db->quoteName('postedithistoryid') . ' DESC');

                $this->db->setQuery($query1, 0, 1);

                $this->db->execute();
                $row1 = $this->db->loadAssoc();

                $rows[$i] = $row;
                if ($row1)
                {
                    $rows[$i]['post_title'] = $row1['post_title'];
                    $rows[$i]['post_modified'] = $row1['post_datetime'];
                    $rows[$i]['post_text'] = $row1['post_text'];
                }
                else
                {
                    $rows[$i]['post_modified'] = $row['post_datetime'];
                }
                $i++;
            }
            //cleanup
            unset($rows_tmp, $i, $rows_tmp, $row, $row1);
        }
        // cleanup
        unset($start_post, $get_as_numrows, $query);

        return $rows;
    }

Es werden zuerst die User komplett, dann die Foren (inkl. Kategorien) komplett, dann die Threads (bekommen jeweils die neue Foren und Usert-ID, dann die Beiträge (bekommen neue Foren, Thread, User und Beitrags-ID)

Das ganze wird über ein jQuery-Ajax gesteuert, welches immer (einstellbar) 20 oder 50 oder 100 Datensätze aus der DB holt, die abarbeitet und danach eine (einstellbare) Pause macht, damit andere Scripte auch was machen können.
 
Zuletzt bearbeitet:
Die Daten werden aufbereitet, (zb. html nach BB konvertieren oder BB nach HTML konvertieren), Usergruppen werden für das Zielsystem gerade gebogen und die Daten werden in die neu DB gepackt,- jeder User, Forum, Thread und Beitrag wird dabei in einer "progress"-Tabelle mit alter und neuer ID festgehalten, so kann ich das ganze auch unterbrechen und Stunden/Tage später weiter machen, ohne das es Datensalat gibt.
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück