1

I have a MySQL 5.7 table that holds activities that users can search through. It will end up holding less than 100,000 rows and inserts will be less than 50/day. This activities table has a structure of:

CREATE TABLE activities (
    activityID int AUTO_INCREMENT, 
    createdBy char, -- a unique AWS ID. eg: "us-east-1:128d0a74-c82f-4553-916d-90053e4a8b0f"
    active bool, 
    city varchar, 
    length int, 
    price  decimal, 
    latitude decimal, 
    longitude decimal,
    CONSTRAINT Activities_PK PRIMARY KEY (activityID)
);

The queries I will be making are constructed depending on the query string sent to the API endpoint. There are 3 'paths' the API can be searched by to return activities.

  • If createdBY is included the query string, Select ... where createdBy = ~some id~
  • If city is included, Select by city, and length if included, and mininmum price if included, and maximum price if included
  • If latitude and longitude are included, search between the 2 points

Indexes I have come up with so far:

  • createdBy, as a B-Tree. Should this be a HASH index because it's a unique identifier?

  • (city, length, price). Since this is a covering index, if the constructed Select query is only searching by city, would it still use the index even though it contains length and price?

    Also, since covering indexes start on the left, should I also include a (city, price, length) index?

  • (latitude, longitude)

Are these acceptable indexes? Should I include an index for the active column? All queries should only return active activities. My guess will be ~90% of rows would be active, while ~10% would be inactive. This answer suggests no.

Thanks so much! Any other suggestions are welcome!

1 Answer 1

1

InnoDB does not support HASH indexes; only B-TREE. You could certainly implement your own with another column called createdByHash. Use something like CRC32 as your hashing function to make it integer-based and save a few bytes per row. Index this column instead and make your queries SELECT .. FROM .. WHERE createdByHash = CRC32(<input>) AND createdBy = <input> (you have to have both parts because there could be collisions with CRC32)

Yes, MySQL uses multi-column indexes in left-to-right order. SELECT city, length, price FROM .. WHERE city = x will still use the index. If you have any range-based queries, the index should put those range'd columns to the right. An index on (A, B, C) and WHERE A=4 AND B>5 AND C=3 will only use the first two columns of that index.

If you are doing a simple greater-than/less-than on the lat/long, what you have will probably be OK. This one is tough since they are both ranged predicates. You may have to experiment on (lat, long) vs (long, lat) in your application.

3
  • 1
    Thanks! Any thoughts on the index for the active boolean column? Commented Apr 9, 2018 at 23:35
  • 1
    For an index on active, if ~90% of the rows will be true and you add where active=1, that index is pointless. But if your query does the inverse (where active!=1), then it could be very helpful because you'd be removing 90% of the rows from consideration. Always think about which column/value will reduce (ie: filter) the number rows the most.
    – utdrmac
    Commented Apr 11, 2018 at 3:04
  • 2
    Since it won't get to the second column in (lat, long), you may as well have just (lat) and (long); the Optimizer will pick between them.
    – Rick James
    Commented Apr 29, 2018 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.