MySQL – How to Force IGNORE ALL Indexes?

indexMySQLmysql-5.6

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes?

I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful.

As for why I (and others) need to do this: For example, I needed to summarize referers statistics by tld like this:

SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
    IGNORE INDEX(domain_name)
GROUP BY tld
ORDER BY c desc
LIMIT 100

…but I always have to look at what indexes are defined or determine what index will be used via Explain. It would be very handy to simply write IGNORE INDEX ALL and simply not care.

Does anyone know the syntax or a hack? (Tens of lines via MySQL definition tables are really not a shortcut).

Added from chat discussion:

Bechmark:

  • no index = 148.5 secs

  • with index = 180 secs and still running with Sending data
    The SSD array is so powerful, that you almost no care about data cache…

Definition for benchmark:

CREATE TABLE IF NOT EXISTS `domains_import` (
`domain_id` bigint(20) unsigned NOT NULL,
`domain_name` varchar(253) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `domains_import`
ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

ALTER TABLE `domains_import`
MODIFY `domain_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

InnoDB, the test with index (no USE INDEX() or similar) is still running, 250 secs, I just killed it.

Best Answer

It's absolutely not clear why you want this but you can use the hint USE INDEX () to tell the optimizer not to use any index. From MySQL docs: index hints

It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error.

Your query becomes:

SELECT count(*) AS c, 
       substring_index(domain_name, '.', -1) AS tld
FROM domains_import 
       USE INDEX ()        -- use no indexes
GROUP BY tld
ORDER BY c DESC
LIMIT 100 ;

Side note: the complex expression:

SUBSTRING(domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2)

can be simplified from 4 function calls to 1:

SUBSTRING_INDEX(domain_name, '.', -1)
Related Question