The CREATE TABLE ` palette ` (
` id ` bigint (20) NOT NULL AUTO_INCREMENT, 
` created_at ` bigint (20) DEFAULT NULL, 
` updated_at ` bigint (20) DEFAULT NULL, 
` deleted_at ` bigint (20) DEFAULT NULL, 
` version ` int (11) the DEFAULT '1', 
` thewire ` float DEFAULT NULL, 
` r ` int (11) the DEFAULT NULL, 
` g ` int (11) the DEFAULT NULL, 
` b ` int (11) the DEFAULT NULL, 
PRIMARY KEY (` id `) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=501096 DEFAULT CHARSET=utf8mb4; 

 select * from the palette where (ABS (r - 188) + ABS (g - 156) + ABS (b - 223) & lt; 100) 

This table has 500000 follow-up data query takes a large number of growth will continue to use the above statements are generally in 0.8 seconds, what better optimization? Thank you

, 188, 156, 223 in the SQL query was introduced into the value of 100 is used to specify an RGB color matching user value, by the user of the incoming RBG if with library records if less than 100 of the absolute value of the RGB the subtraction even match

CodePudding user response:

You can try under the table

CodePudding user response:

Speak or R, G, B type int, set the primary key, do not use the id as the primary key

CodePudding user response:

This query, you of field was calculated, so how to set up the index, a primary key is meaningless, will scan all
So you must adjust the query

Analysis:
The user of the incoming value: r=188, g=156, b=223, maximum allowable error=100
So the range of r + g + b is: (188 + 156 + 100=567 + 100 + 223)
Can use first r + g + b values to narrowing the scope of scanning, the SQL statement rewritten into

 SELECT 
* 
The FROM 
The palette 
WHERE 
R + g + b BETWEEN 188 + 156 + 223-100 AND 188 + 156 + 223 + 100 
AND ABS (r - 188) + ABS (g - 156) + ABS (b - 223) & lt; 100 

Here will say that the r + g + b is calculated, also cannot use the index?
B: yes, so have to modify the table structure
If is mysql5.7 or above, can use to calculate column, and establish the index
If is mysql5.7 version below, then add a column rgb_sum, insert the data maintenance, and establish the index

Final performance, 200 w data in the table, the query takes 0.5 s, environment is 7100/8 g i3 office machine

CodePudding user response:

I also test data of 200 w 0.5
But there is no rgb_sum seems almost this column effect

CodePudding user response:

Direct change, really not easy, you can add fields RGB, this field is directly stored (ABS (r - 188) + ABS (g - 156) + ABS (b - 223) the value of the and increase the index of this field,
And then change the query to
SELECT * FROM the palette WHERE RGB BETWEEN 0 AND 99;