TheUnreal TheUnreal - 2 months ago 19
MySQL Question

Mysql query slow performance

I have a table with 500k rows. I have specific table which takes really long time to run every query.

One of the queries is:

SELECT *
FROM player_data
WHERE `user_id` = '61120'
AND `opzak` = 'ja'
ORDER BY opzak_nummer ASC


the
opzak_nummer
column is a
tinyint
with a number.

EXPLAIN:

enter image description here

Is there any way to improve this query performance and the general of this query/table?

The table name is player_data and includes about 25 columns, most of them are integers with values of stats.

The index is
id
AUTO_INCREMENT.

Answer

You need to run that query, it will alter table and add index. You can read more details here http://dev.mysql.com/doc/refman/5.7/en/drop-index.html

ALTER TABLE pokemon_speler ADD INDEX index_name (user_id, opzak);