Hello.

I have such a problem, I have a very large database, and when I open the page using AJAX (site on angular) I get a date from three different tables (JOIN), and it lasts very long, how to speed up this process, there are different ideas I do not know what will help me more.

You need something like this to show everything at once. Base structure: There are 3 different tables, blocks (white squares), the links themselves (each block has its own links) and the links visited.

I want to use more practical options, if you have any ideas please share :)

Thank you in advance!

Here is the code that highlights all the information to show.

function getData($myId = 0, $moderator){ $stmtP = $this->runQuery("SELECT * FROM pages WHERE moderator=$moderator order by `order`,`last_update` desc"); $stmtP->execute(); $data['pages'] = $stmtP->fetchAll(PDO::FETCH_ASSOC); if (is_array($data['pages'])) { foreach ($data['pages'] as $key => $page) { $page_id = $page['page_id']; $parent_page_id = $page['parent']; if ($parent_page_id > 0) { $parent_page = $this->getPage('page_id', $parent_page_id, $select = "*"); $parent_page['page_id'] = $page_id; $page_id = $parent_page_id; $page = $parent_page; $data['pages'][$key] = $page; } $stmtT = $this->runQuery("SELECT * FROM tabs WHERE page_id=$page_id order by `col`,`order`,`tab_id` desc"); $stmtT->execute(); $tabs = $stmtT->fetchAll(PDO::FETCH_ASSOC); if (is_array($tabs)) { foreach ($tabs as $tabKey => $tab) { $tab_id = $tab['tab_id']; $parent_tab_id = $tab['parent']; if ($parent_tab_id > 0) { $parent_tab = $this->getTab('tab_id', $parent_tab_id); $parent_tab['tab_id'] = $tab_id; $tab_id = $parent_tab_id; $tab = $parent_tab; } $tab['links'] = $this->getLinks($tab_id, $myId); $tabs[$tabKey] = $tab; } } $data['pages'][$key]['tabs'] = $tabs; } } return $data;}function getTab($field, $value, $select = "*"){$stmt = $this>runQuery("SELECT $select FROM tabs WHERE $field=:value");$stmt->execute(array(':value' => $value)); return $stmt->fetch(PDO::FETCH_ASSOC);}public function getLinks($tab_id, $myId = 0, $select = '*'){ $stmt = $this->runQuery("SELECT $select FROM links left JOIN visited_links ON links.link_id=visited_links.v_link_id and u_id=$myId WHERE tab_id=$tab_id order by `order`,`last_update` desc"); $stmt->execute(); return $stmt->fetchAll(PDO::FETCH_ASSOC);}

    1 answer 1

    I think you should create indexes on the fields by which the tables will join. This will significantly speed up the speed of sampling from various tables using JOIN. But for a start, it would be nice to analyze queries using EXPLAIN . You can read about indexes here.