0

I've got a table:

CREATE TABLE `myTable` (
`a` DECIMAL(8,3) NULL DEFAULT NULL,
`b` INT(11) NULL DEFAULT NULL,
`name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`id` INT(11) NOT NULL AUTO_INCREMENT,
`plus` VARCHAR(100) NOT NULL DEFAULT 't' COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`id`),
INDEX `myKey` (`a`, `b`, `name`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2416682;

I run a query:

SELECT * FROM myTable 
order by a, b, name
limit 10000, 8 ;

It is very slow, takes about two seconds.

If I run an explain, it gives back this:

        'id' => 1,
        'select_type' => 'SIMPLE',
        'table' => 'myTable ',
        'partitions' => NULL,
        'type' => 'ALL',
        'possible_keys' => NULL,
        'key' => NULL,
        'key_len' => NULL,
        'ref' => NULL,
        'rows' => 1759263,
        'filtered' => 100.00,
        'Extra' => 'Using filesort',

Now if I set the limit to a lower value, it becomes very fast and explain says it uses myKey:

SELECT * FROM myTable 
order by a, b, name
limit 1000, 8 ;

explain:

        'id' => 1,
        'select_type' => 'SIMPLE',
        'table' => 'myTable',
        'partitions' => NULL,
        'type' => 'index',
        'possible_keys' => NULL,
        'key' => 'myKey',
        'key_len' => '779',
        'ref' => NULL,
        'rows' => 1008,
        'filtered' => 100.00,
        'Extra' => NULL,

What can I do to make the select faster when the limit has high numbers?

I'm suprised the key is not used all the time, independent of the limit value. Is this normal?

MySQL version is 5.7.20-log.

Thanks

bala529
  • 11
  • 2

1 Answers1

1

You can force the use of the index by doing the following:

FORCE INDEX (myKey)

FORCE INDEX mySQL ...where do I put it?

Adrián García Campos
  • 399
  • 3
  • 18
  • Thanks, it works quite well! Now the problem is that if I write a high number in the select (like: limit 1000000, 8), the select takes about two seconds. Even with the key used. Is there a way to make it faster? – bala529 Jan 03 '18 at 19:43
  • No idea actually, I'd have to test it. If I helped you, please consider accepting my answer :) – Adrián García Campos Jan 03 '18 at 19:46
  • I already tried to do that, but I have got only 6 reputations and I would need 15 to be able to vote in a displayable way. But the upcoming dialog told me my vote was recorded. I guess it will be counted in when I reach 15 reputations. – bala529 Jan 03 '18 at 20:02