Optimize query many field mysql

Question

I'm confused about how to chose best index for my table:

Have this table:

Hotel
-has_1
...
-has-5
-nota_AVG
-nota_1
-nota_2
-nota_3

and this

Nota_hotel
-nota_1
-nota_2
-nota_3
-id_hotel

Field with name "nota_*" from hotel is update from a trigger on table Nota_hotel and this field changed frequently.

My need do some query like

select * from hotel where has_X = true or has_Y=true order by nota_Z

where my clause "WHERE" can have 1 has_X or many has_* fields in query depend on what checkbox is selected.

I want know what is best practice to put index, add for every field "nota_" a index and for fields "has_" create a single index(has_1,...,has_5) or add for every field "has_" a index too, and if have this much index is possible to strangle my MySQL server ?

    create table hotel(
id int primary key auto_increment,
nume varchar(255),
index hotel_nume_index(nume),
nume_oras varchar(100),
nume_zona varchar(100),
nume_tara varchar(100),
foreign key (nume_oras,nume_zona,nume_tara) references oras(nume,nume_zona,nume_tara) ON DELETE CASCADE,
descriere text,
stele int,
map_x double,
map_y double,
has_sauna TINYINT(1),
has_piscina TINYINT(1),
has_parcare TINYINT(1),
has_restaurant TINYINT(1),
has_fitness TINYINT(1),
distanta_obiectiv double,
code_api1 varchar(255),
code_api2 varchar(255),
code_api3 varchar(255),
intern TINYINT(1),
nota_hotel double,
nota_restaurant double,
nota_locatie double,
nota_conditii double,
nume_comision varchar(100),
foreign key (nume_comision) references comision(nume)
);



 create table nota_hotel(
fb_id varchar(100),
data DATETIME,
nota_restaurant double,
nota_locatie double,
nota_conditi double,
comentariu text,
nume_user varchar(255),
id_hotel int,
foreign key (id_hotel) references hotel(id) ON DELETE CASCADE
);

Here is full definition

How about 2 create table statements to clue us in. Perhaps u need basic help with table creation — AsConfused
– AsConfused, Commented Jun 5, 2015 at 14:40
I Edit and ADD full definition, ignore rest of table, only there fields is important for moment — Laurentiu
– Laurentiu, Commented Jun 5, 2015 at 14:46
Thank you for edit. Perhaps it is a spoken language thing. What does nota mean and that may explain why it is a floating point numeric type — AsConfused
– AsConfused, Commented Jun 5, 2015 at 14:53
nota is someting when you rating there hotel, each row from table nota_hotal is a rating come from a user, and field nota_ from table hotel is field average from table nota_hotel , but this is updated from a trigger, i want know who is best way to add index or to split table — Laurentiu
– Laurentiu, Commented Jun 5, 2015 at 14:57
So hotel double rating is an average saved and indexes on hotel notaZ ratings are for quick sort by's (and nothing to do with FK). Sounds good to me — AsConfused
– AsConfused, Commented Jun 5, 2015 at 15:02

Drew · Accepted Answer · 2015-06-05 15:49:35Z

1

this is a work in progress answer. will add to it.

let's say you have 5 has_ columns, like has_restaurant, has_pool, has_pool and searches are on true types like before, as in 'it is true that it has a pool'

for starters, convert them into one column using powers of 2. let us call that hasX and its column value is a bit-wise OR of the below (that are not columns)

has_restaurant (2^0)   1
has_pool (2^1)   2
has_wifi (2^2)  4
has_piscina (2^3)  8
has_sauna (2^4)  16

so if the hotel has wifi and sauna then the hasX column will contain (at least) the bits turned on with value 20, but could contain a value of 21 (has a restaurant too). but the search was on 'i want wifi and sauna' so it finds the 21 too. it is a mask

so at this point we have added one index to hotel, on column hasX

Edit I just bombed on this notion as i assumed i could craft a bit-wise search and that seems quite impossible after reading about it. The following seems to be the best work-up

CREATE TABLE hotel ( 
hotel_id int primary key auto_increment PRIMARY KEY,
/* some more fields... */ 
); 

CREATE TABLE hotel_flags ( 
flag_id TINYINT UNSIGNED NOT NULL 
, description VARCHAR(100) NOT NULL 
, PRIMARY KEY (flag_id) 
); 

**the following is the intersect table**
CREATE TABLE hotel_flags_intersect ( 
hotel_id int not null
, flag_id TINYINT UNSIGNED NOT NULL 
, PRIMARY KEY (hotel_id, flag_id) 
, UNIQUE INDEX reverse_pk (flag_id, hotel_id) 
);

humbly borrowed from http://forums.mysql.com/read.php?24,35318,35640#msg-35640

edited Jun 5, 2015 at 15:49

answered Jun 5, 2015 at 15:21

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Laurentiu Over a year ago

you idea is very good, but here how can be sure if I have many field can't make a mistake to have some value from 2 different comibation columns ?

Drew Over a year ago

updating my answer then to your comment.

Drew Over a year ago

ok to answer your comment, even if bit-wise were to work that is just staring at your code long enough and pounding on it. might not seem like an answer to your comment

Laurentiu Over a year ago

I was do some calculations if I use power of 2 I will take 2 combination with same value but I don't, your idea to make 1 column with index is very good and seam to have nice speed, thank you very much !

Drew Over a year ago

be careful testing it in your implementation, that is, if they definitely want wifi and sauna, will it pick up value 21 (that has restaurant also). if it bombs, look at bottom of my answer. good luck

|

Collectives™ on Stack Overflow

Optimize query many field mysql

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related