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?
