Work with MyISAM table with the number of records from 10'000'000?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
8 views
There is a table where records are kept about the goods, just the goods about 10'000'000 — the number can grow to 20'000'000.

The output is working fine but the customer wants another admin to the database for the analysis of products, vendors and other things — it will look like the filters for each dB field.


The request to create the table:
CREATE TABLE `suppliers_store` (
`id_suppliers_store` int(11) NOT NULL AUTO_INCREMENT,
`id_suppliers` int(11) NOT NULL,
`dt` date NOT NULL,
`name` varchar(255) NOT NULL,
`code` varchar(255) NOT NULL,
`price` double NOT NULL,
`code_suppliers` varchar(255) NOT NULL,
`count` int(11) NOT NULL DEFAULT '0',
`producer` varchar(255) NOT NULL,
`weight` varchar(255) NOT NULL,
PRIMARY KEY (`id_suppliers_store`),
KEY `NewIndex1` (`id_suppliers`),
KEY `NewIndex2` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8



Here is a sample query conditions that can generate a filter:
select ss.*, s.delivery
from suppliers_store as ss
join suppliers as s on ss.id_suppliers = s.id_suppliers
where (ss.dt >= '2010-02-01 00:00:00' and ss.dt <= '2011-02-01 23:59:59') and
(ss.name like '%bar%' or ss.code like '%panel%') and
(ss.price > 1000) and
(ss.count > 0)
LIMIT 50



Actually these filters and make mysql every time wool the whole database, indexes on fields, as I understand it, is not particularly helpful.


Early experience with such volumes was not, as far as mysql is suitable for this purpose?

How can I increase the search speed at the plate?
by | 8 views

6 Answers

0 like 0 dislike
(optional step:
MyISAM — dump.
Database is in InnoDB.
)
About fulltext Forget — you too lazy to deliver, not cured.
\r
As a search index, use ultrafast opencarry Mature sphinx.
by
0 like 0 dislike
Somehow you misunderstand DBMS.
by
0 like 0 dislike
1. The fields date and price indexes, (then must go to index)
\r
2. For fields with strings — two options — either not to seek to % in the beginning (not going to go, then by index)
\r
or is a separate sign with the words. When you INSERT/UPDATE it to spell all the words in the rows. E.g. insert into words (id, type, word) select suppliers_store.id, 'name', 'panel' and is the index of (type, word id). ACC. the search is performed on this plate without ispolzovaniya %.
by
0 like 0 dislike
> indexes on fields, as I understand it, is not particularly helpful
\r
This is why it suddenly?
by
0 like 0 dislike
Think maybe there's a way to make another sign with the cache, let's say the text fields can be split by spaces and put in another plate with reference to the desired entry, though the lines will be more accurate but a comparison would work much faster than like You. But then the constraint will search precisely according to the words, you can not search for "pan" or "human"
by
0 like 0 dislike
There is a table where records are kept about the goods, just the goods about 10'000'000 — the number can grow to 20'000'000.
\r
I have asked only one question, what will happen "if" the number will increase 20000000, 40000000 to say? And the "goal" is not so unreal...
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
4 answers
0 like 0 dislike
3 answers
110,608 questions
257,186 answers
0 comments
27,917 users