select product.* from product where
product_id in ( select product_id from product_options where option_id = 5 and option_value = "Красный" )
and
product_id in ( select product_id from product_options where option_id = 6 and option_value = "35" )
select product.product_id, count(product_options.id) cnt from product
join product_options on product_options.product_id = product.product_id AND ((option_id = 5 and option_value = "Красный" ) or (option_id = 6 and option_value = "35" ))
group by product.product_id
having cnt = 2
select
...
from job
join status on status.jobID = job.jobID
join worker on worker.workerID = status.workerID
join client on client.clientID = client.clientID
join responsible on responsible.responsibleID = job.responsibleID
select
name
from table
where
(element = "Ag" and Min <= 0.007 and 0.007 <= Max)
OR
(element = "B" and Min <= 0.002 and 0.002 <= Max)
OR
(element = "Fe" and Min <= 0.06 and 0.06 <= Max)
OR
(element = "Mg" and Min <= 0.025 and 0.025 <= Max)
OR
(element = "Cu" and Min <= 0.01 and 0.01 <= Max)
group by Name
having count(ID) = 5
Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
Increase Log File Size (innodb_log_file_size) to 2047M
support separate tablespace files for individual InnoDB tables (enable innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
Increase innodb_read_io_threads to 64
Increase innodb_write_io_threads to 64
Increase innodb_io_capactity to 10000
select
films.*
from meta
left join films on films.id = meta.id_films
where meta_value in (13,15,27)
group by id_films
having count(meta.id) = 3
select
films.*,
count(meta.id) as cnt
from meta
left join films on films.id = meta.id_films
where meta_value in (13,15,27)
group by id_films
where tmp_id between 400000 and 400100
- в вашем случае вероятно не подойдет.UPDATE TABLE sp_germany AS a
JOIN TABLE sp_germany_small AS b ON b.product_artikul = a.product_artikul
SET a.product_price = b.product_price
WHERE coll='чето там' and not (coll = 'asd' or coll like 'asss%' )
LEFT JOIN oc_product_attribute p2a0 ON (p2a0.product_id=p2a.product_id)
JOIN oc_product_attribute p2a0 ON (p2a0.product_id=p.product_id and p2a0.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%')
select product_id from oc_product_attribute p2a0 where (p2a0.product_id=p.product_id and p2a0.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%')
select * from oc_product where product_id IN (1,2,3 ...)