Working with MyISAM table with 10'000'000 records?

Question

MySQL

There is a table where records of goods are stored, in total there are about 10'000'000 goods - the number can grow to 20'000'000.
The output works fine, but the customer also wants an admin panel for this database to analyze goods, suppliers and other things - it will look like filters for each database field.
Query to create a table:

CREATE TABLE `suppliers_store` (<br/>
 `id_suppliers_store` int(11) NOT NULL AUTO_INCREMENT,<br/>
 `id_suppliers` int(11) NOT NULL,<br/>
 `dt` date NOT NULL,<br/>
 `name` varchar(255) NOT NULL,<br/>
 `code` varchar(255) NOT NULL,<br/>
 `price` double NOT NULL,<br/>
 `code_suppliers` varchar(255) NOT NULL,<br/>
 `count` int(11) NOT NULL DEFAULT '0',<br/>
 `producer` varchar(255) NOT NULL,<br/>
 `weight` varchar(255) NOT NULL,<br/>
 PRIMARY KEY (`id_suppliers_store`),<br/>
 KEY `NewIndex1` (`id_suppliers`),<br/>
 KEY `NewIndex2` (`dt`)<br/>
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Here is an example query, with the conditions that the filter can generate:

select ss.*, s.delivery<br/>
from suppliers_store as ss<br/>
join suppliers as s on ss.id_suppliers = s.id_suppliers<br/>
where (ss.dt &gt;= '2010-02-01 00:00:00' and ss.dt &lt;= '2011-02-01 23:59:59') and <br/>
 (ss.name like '%панель%' or ss.code like '%панель%') and<br/>
 (ss.price &gt; 1000) and<br/>
 (ss.count &gt; 0)<br/>
LIMIT 50

Actually, these filters make mysql scan the entire database every time, field indexes, as I understand it, will not help much.
Previously, there was no experience with such volumes, how suitable is mysql for these purposes?
How can I increase the speed of searching on a plate?

Answer 1

(Optional step:
MyISAM - To the dump.
Base - in InnoDB.
)
Forget about fulltext - it will give you so much hemorrhoids, you won't recover.
Use the mega-fast open-source mature sphinx as your search index.

Answer 2

Somehow you misunderstand the DBMS.

Answer 3

1. Fields with dates and prices - indexes (then it should go through the indices)
2. For fields with strings - two options - either do not search with% at the beginning (then it will not go by the index)
or a separate plate with words is made. At INSERT/UPDATE all words in lines are written to it. For example insert into words (id, type, word) select suppliers_store.id, 'name', 'панель', an index is made (type, word, id). Resp. the search is performed on this plate without using %.

Answer 4

> indexes on fields as I understand, especially will not help
It from what it suddenly?

Answer 5

Think it might be possible to make another table with the cache, let's say text fields can be broken up by spaces and stuffed into another table with links to the desired entry, although there will be more lines, but an exact comparison will work much faster than like in you. But then the restriction will be the search clearly by words, it will not be possible to search for "pane" or "human"

Answer 6

There is a table where records of goods are stored, in total there are about 10'000'000 goods - the number can grow to 20'000'000.
I would ask, with only one question, what will happen “if suddenly” the number increases not to 20,000,000, but, say, to 40,000,000? And judging by the "statement of the problem" - this is not so unrealistic ...