0

I have a table "picture" with 255 records for picture colors. I have a table "colors_mixed" with 23897 records. I need to find a closest mixed color to all picture colors, so I wrote a query:

SELECT m.id
    FROM picture AS p
    INNER JOIN colors_mixed AS m ON 
      (ABS(m.red - p.r) + ABS(m.green - p.g) + ABS(m.blue - p.b))
      = (select min(abs(red - p.r)
                  + abs(green - p.g)
                  + abs(blue - p.b)) from colors_mixed)
    WHERE p.id BETWEEN 1 AND 10

It runs less than 1 second. If I use

WHERE p.id BETWEEN 1 AND 150

it runs about 4 seconds. But if I use

WHERE p.id BETWEEN 1 AND 200

or do not use "WHERE" at all, it runs for a minute, 2 minutes, and I just do not want to wait more because it must be done in 5 seconds. Same thing happens if I use reverse order -

WHERE p.id BETWEEN 200 and 255

works ok, and "BETWEEN 50 and 255" just does not stop.

Advice please, how can I fix it?

Rick James
  • 135,179
  • 13
  • 127
  • 222
user3565412
  • 81
  • 2
  • 10
  • Add an index on picture.id? – rustyx Aug 06 '20 at 14:51
  • Please show an EXPLAIN. My guess is that your join uses functions and cannot be indexed, so the query has to evaluate the calculation for each record matching your wear clause, and this will expand in time linearly with the number of records matching your wear clause. Is there a way of re-writing the join? – Neville Kuyt Aug 06 '20 at 15:49
  • Please provide `SHOW CREATE TABLE` and `EXPLAIN SELECT`. You have a challenging problem. – Rick James Aug 06 '20 at 17:58

2 Answers2

0

You could try and add an index to picture.id. This will make your querys a lot faster. This is the syntax you should be using.

ALTER TABLE tbl ADD INDEX col (col)

In your case you should run something like

ALTER TABLE picture ADD INDEX id_index (id)

or try it in your other table as well.

You can find more information about indexes here: https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables#:~:text=Creating%20Indexes&text=The%20statement%20to%20create%20index,the%20index%20must%20be%20distinct.

Jorge Saravia
  • 11
  • 5
0

Sorry, it was some bug of HeidiSQL client. I tried other client and now it's ok.

user3565412
  • 81
  • 2
  • 10