Help to optimize an index in mysql

1

I have a relationship website and for years I've tried to optimize this query. Almost every day the site hangs because of it.

User table structure

CREATE TABLE 'usuarios' (
  'id' bigint(20) NOT NULL,
  'nome' varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  'cidade' varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  'latitude' float(6,2) NOT NULL DEFAULT '0.00',
  'longitude' float(6,2) NOT NULL DEFAULT '0.00',
  'ano_nascimento' smallint(4) NOT NULL DEFAULT '1997',
  'genero' tinyint(1) NOT NULL DEFAULT '1',
  'genero_procuro' tinyint(1) NOT NULL DEFAULT '2',
  'idade_minima' tinyint(2) NOT NULL DEFAULT '18',
  'idade_maxima' tinyint(2) NOT NULL DEFAULT '80',
  'unix_timestamp_online' int(10) NOT NULL DEFAULT '0',
  'usuario_bloqueado' tinyint(1) NOT NULL DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indexes

ALTER TABLE 'usuarios'
ADD PRIMARY KEY ('id'),
ADD KEY 'indice_composto'(
    'unix_timestamp_online',
    'usuario_bloqueado',
    'genero',
    'genero_procuro',
    'idade_maxima',
    'idade_minima',
    'ano_nascimento',
    'latitude',
    'longitude'
) USING BTREE;

Query example

SELECT id, latitude, longitude 
FROM usuarios 
WHERE genero=2 
AND id NOT IN (11111111,2222222) 
AND (ano_nascimento BETWEEN 1961 AND 1999) 
AND (latitude BETWEEN -25.14 AND -16.13) 
AND (longitude BETWEEN -54.87 AND -45.86) 
AND usuario_bloqueado=0 
AND idade_minima<=35 
AND idade_maxima>=35 
AND genero_procuro IN(0,1) 
ORDER BY unix_timestamp_online DESC 
LIMIT 20

Summarizing the conditions of the sample query ..

  • I am masculino=1 and am looking for users of the genre feminino=2
  • I exclude some% with%
  • They were born between ids
  • Are in 1961 AND 1999 , latitude ...
  • Do not have any type of longitude
  • My bloqueio=0 must be acceptable to these users
  • My gender also needs to be acceptable ( idade=35 , male = 1)
  • Sort by displaying users who are online first

I think the composite index that was created is not so beautiful, but in every way I tried, that was the one that brought least slow queries. It has query that takes less than 1 second, another takes 4, another 10, 20, and so on. It's hard to tell what I've tried since there were almost all index combinations ...
I may be missing out on just one detail, index, select, maybe need to look for another bank, I do not know, any help is welcome. Thank you.

EDIT:

Explain the example query

id: 1
select_type: SIMPLE
table: usuarios
partitions: NULL
type: index
possible_keys: PRIMARY
key: indice_composto
key_len: 19
ref: NULL
rows: 20
filtered: 0.25
Extra: Using where; Using index

This query takes an average of 0.0020 seconds

If I change the year_increment condition to ( todos=0 )

The explain is identical and the query takes on average 9.1376 seconds

    
asked by anonymous 26.07.2017 / 07:48

0 answers