Optimize query/table for speed MySQL?

You can always run EXPLAIN on your query in question to find missing indexes + other optimization clues.

SQL query: EXPLAIN SELECT id,sender,recipient,message,priority,flash_message FROM movistar_mt3_saca_nic WHERE process=0 ORDER BY priority DESC, id ASC LIMIT 10;
Rows: 1

id      select_type      table      type      possible_keys      key      key_len      ref      rows      Extra      
1      SIMPLE      movistar_mt3_saca_nic      ref      procesados      procesados      4      const      716107      Using where; Using filesort

The query is finding 716,107 rows, sorting them, and then returning the first 10 of the sorted list.  Depending on your system and configuration 3.5 seconds may not be unreasonable.

Can you apply a second filter to reduce the number of rows from nearly 3/4 million to about 100,000?  Or less?

I believe this server is runnign SSD disks and 128GB of ram with 40 cores?

Can you create a composite index on the data over the key items?

CREATE INDEX idx_1 ON  movistar_mt3_saca_nic (process, priority DESC, id ASC)

I'd test this on an offline system.  It may help the report, but it could also create quite a bit of overhead with storing/updating rows.

I actually removed ALL indexes and it lowered the query time from 3.5s to 1.1

This is our my.cnf

 

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 7000
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 108G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

Select allOpen in new window

any thoughts on config?

Hello Mario,

Here are quick comments:

* Comment for table structure:
- avoid index on `message`
- don't include PK in your index -> KEY `index13` (`priority`,`id`)
- is the `priority` field boolean or can be covered with lower datatype - say tinyint ?
- all your integer columns are signed, make them unsigned to make full use of the range
        `process` int(11) UNSIGNED NOT NULL DEFAULT '0'

* "I actually removed ALL indexes and it lowered the query time from 3.5s to 1.1"
- not a good idea. You can improve WRITEs without indexes but not READs. And InnoDB table without PK is not good atall.
- You're doing full table scan, reading the table lines one by one. This is fast if your table is in the memory. I see your have good amount of innodb-buffer-pool. If you run your query with SQL_NO_CACHE (SELECT SQL_NO_CACHE ...), you should see the real picture without index.

* About query:
Do you really want to process all the records? Say the record created 2 years ago? If not, create a filter on date which will substancially reduce the data to process:
eg:
SELECT ... WHERE send_time>date_sub(CURDATE(), interval 30 days)

For whatever solution you choose -> Do NOT add a Primary Key in any secondary index.

Hi,

>For whatever solution you choose -> Do NOT add a Primary Key in any secondary index.

Adding additional columns to an index ( whether it belongs to a primary key or not) is beneficial to all queries using multiple columns in a where clause where those columns match to a columns of an regular index (where duplicates are allowed). This has been proven to be true for all database systems.
Same columns in unique indexes and primary keys (which are unique) is a whole other matter.
Having only one column in an index is not good practice when it comes to database and query performance.

Regards,
    Tomas Helgi

Hello Tomas,

Though generally speaking the idea you're proposing is right but for MySQL this is what documentation says:

In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

Ref: https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

so "( whether it belongs to a primary key or not) " -> This makes a difference in case of Innodb (MySQL).

Regards.