Searching for a large number of records in mysql

Question

MySQL

There is a database (mysql) with a large number of records (more than a million) in the format || *id* || *word* || *value* ||. There is an array with a list of values ​​to search in column1. That is, the

DB Array array, like the database itself, can be very large ... how to create a query so that by minimally loading the database, get the values ​​from the value column to the words from the $word array

|| *id* || *word* || *value* ||
|| 1 || val1 || 12 ||
|| 2 || val2 || 13 ||
|| 3 || val3 || 33 ||



$word[] = "val1";
$word[] = "val3";


Answer 1

Are there any indexes?

Answer 2

1. what is the percentage of words to search in the array of the total number of records?
2. If less than 1% (I don’t remember to what value the use of the index is relevant), then it can make a two-way combination of the form:
1) create a temporary table with one column
2) fill it with an array
3) select everything you need by writing JOIN the created temporary table with the main one, by which we are looking for, connecting them by the value of the word field?

Answer 3

WHERE `tabulka`.`sloupec` IN ('a','b','c')?

Answer 4

If possible, store record IDs in an array, fetching them should be faster.

Answer 5

By the way, consider concatenating the array into 1 string and making 1 query for any of the words in the string. If the string turns out to be long, it can be divided into several, in any case, the query will become faster. WAYS, today at 13:56

Answer 6

I can be wrong, but a simple request for a selection of 1M rows without sorting should not particularly load the base.
If the *word* column contains not very large fixed strings. length, then you can really try a covering index or move the entire table into RAM (depending on the amount of RAM, you can run into swap and make it worse)
You can try using NoSQL - like here for example tokarchuk.ru/2010/10/nosql-in-mysql -improve-mysql/
You can use some key-value storage like radish ...