CODEWITHSUNDEEP
mysqldatabase-indexes
Alain Zelink
Alain Zelink

Reputation: 899

Mysql index on values != 0

I've a large table which contains events. Most of them have got a longitude + latitude. But some don't.

When I look for a list of events, I always include WHERE longitude!=0 AND latitude!=0

Is it possible in an index or by some other way to have a pre-filter which would get rid of non geolocated events.

PS: I need to keep those non geolocated events in my table. 

SELECT * FROM events WHERE start_time > NOW() AND latitude != 0 AND longitude != 0

Upvotes: 0

Views: 54

Answers (2)

BYUBadger
BYUBadger

Reputation: 53

I think the best answer here would be to create a view. You can interact with a view in almost the same way as a table (INSERT, UPDATE, DELETE, etc.), but you can select a subset of that table to interact with in the view. Here's the creation syntax:

CREATE VIEW [you view name] AS
SELECT * FROM events WHERE start_time > NOW() AND fblatitude != 0 AND fblongitude != 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In most cases, an index would not make a difference to performance. The primary issue is selectivity. That is, what proportion of events are geolocated and what proportion are not.

When scanning a table, the database needs to read all the pages. One way that an index makes things faster is by reducing the number of pages that need to be read. But, this can be counter-intuitive. If a typical record is 80 bytes, than about 100 will fit on each page. So, even if only 5% of the events are geo-located, then there is a good chance that all pages will need to be read. The average page would have 5 such records. In other words, the index isn't saving much work (and can even be causing more work, but that is another issue).

There are caveats to this. Creating a primary key index on the table with a "geo-located" flag as the first element affects the page layout. All pages except at most one will have only geolocated records or non-geolocated records. This will provide a performance boost.

For your particular query, the best index is probably events(start_time). Because of the select *, the query will need to go to the data pages anyway to fetch the data and can return the latitude and longitude at the same time.

Upvotes: 1

Related Questions