0

I've got a simple query:

SELECT distinct state FROM customers WHERE country = 'us'

The idea is to get a list of states in the US that customers live in.

I've got around 800k rows, and an index on state and on country. This query takes about 4s to run, which is way too long, as this needs to be in real time.

If I remove the WHERE clause, it figures it out almost instantly. I looked into why, and when I run an explain on the query with the WHERE clause, it does not use the state index. When I get rid of the WHERE clause, it starts using the state index again. Why is this happening, and is there a way I can make it use both indexes?

I've browsed other questions on SO that seem similar, but it seems like for the ones I found, they were very complicated queries, and the solution was just a query rewrite. This is so simple, though, I think it gets to the heart of the problem.

Update:

I have found that if I do an index on us_state, it works in about .5s, which is good enough. I'd like to have multiple parts to the WHERE clause at some point, though, so making a compound index for all the parts is still not optimal. As such, I leave this question open looking for a better solution.

CreateTable

CREATE TABLE `customers` (
`country` varchar(5) DEFAULT NULL,
`st` varchar(50) DEFAULT NULL,
KEY `state` (`state`),
KEY `country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
user3413723
  • 11,147
  • 6
  • 55
  • 64
  • just checking, because sometimes the mysql query planner is bonkers, do you get a similar result for `SELECT state FROM customers WHERE country='us' GROUP BY state` ? – hobbs Feb 09 '16 at 19:10
  • Yes, unfortunately `group by` takes around 4s as well. – user3413723 Feb 09 '16 at 19:12
  • i would suggest you add a common numerical index for country='us' and do a where on that. MySQL is pretty bad when it comes to dealing with anything text. Numbers, it can handle well. Also, what are the cardinalities for those indices? – RisingSun Feb 09 '16 at 19:19
  • post your `CREATE TABLE` statement please – Alex Feb 09 '16 at 19:42
  • Hi Alex, I made this table a long time ago using HeidiSQL. It is a complicated table with about 20 columns. What part of it do you want to see? – user3413723 Feb 09 '16 at 19:48
  • I want to see *create table* statement. to get that you should run this query `SHOW CREATE TABLE customers` – Alex Feb 09 '16 at 20:09
  • 1
    Either you have an index on a column that doesn't actually exist, or you mangled something when you redacted the create table for SO. Can you show the *actual* output of `SHOW CREATE TABLE`? – hobbs Feb 09 '16 at 22:59
  • your create statement has no sense. either you are not attentive or you are lying :-) And I did not ask for table *all_wellbore_data* I did ask for *customers* – Alex Feb 10 '16 at 19:19

1 Answers1

0

This answer should provide you with a solution: https://stackoverflow.com/a/586834/3299157

In short, run the Analyze function and see if that speeds it up.

ANALYZE TABLE CUSTOMERS;

http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

Community
  • 1
  • 1
Ageonix
  • 1,748
  • 2
  • 19
  • 32