PHP - mysql search in milions records

Question

i have 13 tables with +- 100 000 records in each, same columns, but if i want search some records by column in all tables it takes +- 30sec... its so long :( if i search in 1 table first refresh takes 2-3sec secong refresh takes 0.3sec thats is good for me but only for one table and i need search and get result from all. so at all i have 1 300 000 records at now and i need some fast search :( any help? ty a lot

public function getTablesAoiData(){
        return $this->database->query("SELECT TABLE_NAME as `table` FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%aoiData%'");
    }

public function searchBarcode($barcode){
        $result = array();
        foreach($this->getTablesAoiData() as $key => $value){
            $result[$key] = $this->database->query("SELECT * FROM `". $value->table ."` WHERE `Barcode`=" .$barcode)->fetch();
        }
        return $result;

    }

i have Id-primary key and Barcode is not unique cuz i can have more records with same Barcode — Zdenek Barta, Jun 12 '17 at 07:30
you think if i had 1 table with more then 1 300 000 records it will be fast search? — Zdenek Barta, Jun 12 '17 at 07:43

score 0 · Answer 1 · answered Jun 15 '17 at 08:39

First of all, you didn't posted table structure. You problably don't have proper indexes, which I can't tell for sure without knowing complete create table.

Second thing is, that searching across multiple tables will always be slow. You can use UNION to make it faster, but will it still be slow and temporary table in database will be created which is not good in most cases. If you can't update your structure to have data in one table, reather use some sort of service designed for indexing and searching data like ElasticSearch

score -1 · Answer 2 · answered Jun 12 '17 at 08:51

-1

its just need to be indexed that Column Barcode and now from 30sec search in all tables i got 4ms so ty for a question if i index it :D

answered Jun 12 '17 at 08:51

Zdenek Barta

43
6

PHP - mysql search in milions records

2 Answers2