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 genrefeminino=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