How can I speed up the process of retrieving information about downloaded files? At the moment, information about the downloaded files is in MySQL.

Here is what it looks like now:

$stmt = $this->mysqli->stmt_init(); $stmt->prepare("SELECT id FROM daemon_zip WHERE dir_city = ?;"); $stmt->bind_param("s", $dir_city); $stmt->execute(); $res = $stmt->get_result(); $res = $res->fetch_assoc(); if ($res == null) { // ... }

It is necessary to optimize the process of retrieving information about the downloaded files and checking whether the file is downloaded or not, at the moment it’s checking whether the file is downloaded or not takes a lot of time, from 0.1s

PS: At the moment there are about 140 thousand 8 million records in the database.

Closed due to the fact that the issue is too general for participants aleksandr barakin , user194374, Denis , Bald , VenZell 18 Jul '16 at 7:58 .

Please correct the question so that it describes the specific problem with sufficient detail to determine the appropriate answer. Do not ask a few questions at once. See “How to ask a good question?” For clarification. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • one
    Ask a question more fully deployed - Naumov
  • It is necessary to optimize the process of storing information about downloaded files and checking whether a file is downloaded or not, at the moment checking whether a file is downloaded or not takes a lot of time, from 0.3s - users
  • Since you are sampling the url field and I do this as I understand it often enough, you can try to make an index on this field. And watch. May well help in the search. - KoVadim 1:54 pm

1 answer 1

Store the data in a file in json format . Reading (without criteria: select all) and writing to the file an order of magnitude faster.

Or hang the index on those columns that are being sampled. At the moment, this is the url column.

 ALTER TABLE `table_name` ADD INDEX `indx_name` (`url`)

Always use prepared queries .

MySQL DBMS supports prepared queries. Prepared (or parameterized) queries are used to increase efficiency when a single query is executed multiple times.

  • So the author's search, rather than reading-writing, slows down. Besides, having done everything in json, you still need to do your own search. Plus, to solve the issue of locking a file, so that two processes do not write at the same time. - KoVadim 1:56 pm
  • @KoVadim Anyway, working with the file is faster. I wrote the second option about the index. Then everyone has their plus and minus. You have to sit and check both options, And do not be lazy :) - Vanya Avchyan
  • one
    I'm afraid that you so simply can not write faster on the files than on the base, if the records will be even 100k. - KoVadim
  • Yes, using the index helped and the file is not very suitable - users