1

My question is how can i speed this up. There should be more elegant way to handle it. The inner select is done in 0,038 sec but this one is done in 6,007 sec i dont know how can i improve this performace 

select * FROM table
where number1 in (
SELECT number1 
FROM table
WHERE `date` = 'yyyy-mm-dd' 
AND value1 = 'variable1' 
AND value2 = 1
)

the thing is that i need range of values from the same table if one contains in the value2 the variable 1

so from table like that 

id|number1| value1   | value2
1 | 11403 | exempl1  |  null
2 | 11404 | exempl1  | 1
3 | 11404 | exempl1  | null
4 | 11405 | exempl1  | null
5 | 11405 | exempl1  | null

i get only this 

id|number1| value1   | value2
2 | 11404 | exempl1  | 1
3 | 11404 | exempl1  | null
arudiel
  • 13
  • 4

1 Answers1

0

You can convert it into Correlated Subquery with Exists. MySQL optimizer may be able to use Indexes (if defined) in this case.

SELECT t1.* 
FROM table AS t1
WHERE EXISTS( SELECT 1 
              FROM table AS t2
              WHERE t2.number1 = t1.number1 AND 
                    t2.`date` = 'yyyy-mm-dd' AND 
                    t2.value1 = 'variable1' AND 
                    t2.value2 = 1 )

If indexes are not defined, you can define a Composite Index on (number1, date, value1, value2), for better performance. 

ALTER TABLE `table`
ADD INDEX comp_idx1(number1, `date`, value1, value2);

Another optimization possibility is to fetch only those columns which are really required in your application code. Avoid using SELECT *. You may read: Why is SELECT * considered harmful?

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • there is no improvement in performance, it works but still over 6 seconds downtime. – arudiel Nov 20 '18 at 09:06
  • @arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer. – Madhur Bhaiya Nov 20 '18 at 09:09
  • At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows. – arudiel Nov 20 '18 at 10:32
  • @arudiel check my solution; I have never used `ID` at all. I dont know why are you matching `ID`. It will kill all the purpose – Madhur Bhaiya Nov 20 '18 at 10:33
  • @arudiel add the Index (`alter table` statement) which I have mentioned in my answer. – Madhur Bhaiya Nov 20 '18 at 10:35
  • I have indexed only Id , date , value1 columns. So the end code looks like t2.date = t1.date AND t2.date = 'yyyy-mm-dd' AND – arudiel Nov 20 '18 at 10:41
  • @arudiel query is fine. You are concerned about performance, right ? – Madhur Bhaiya Nov 20 '18 at 10:42
  • @arudiel then please run this statement to add an index: `ALTER TABLE your_table_name_here ADD INDEX comp_idx1(number1, date, value1, value2);` – Madhur Bhaiya Nov 20 '18 at 10:48
  • Done and i think worked. But i dont understand this i have indexes on those fields only separated indiwidualy. Whats the diference ? – arudiel Nov 20 '18 at 11:40
  • @arudiel Go through this: https://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html – Madhur Bhaiya Nov 20 '18 at 11:41