Mysql query optimization for Distinct count

Question

I had a query like this

select count(distinct sessionKey) as tot from visits

But it takes too much time for execution 48512 ms Now.
Within some months data in table will become double of the current amount of data.How I can optimize this query

This is my table structure

enter image description here

[Speeding up row counting in MySQL](http://stackoverflow.com/questions/1332624/speeding-up-row-counting-in-mysql) — Quicksilver, Oct 19 '12 at 09:01
is its right setting index to fields slow down the insert operation? — Renjith K N, Oct 19 '12 at 09:09

score 4 · Accepted Answer · answered Oct 19 '12 at 08:58

4

add an INDEX in your column SessionKey and it will improve its performance.

ALTER TABLE visits ADD INDEX (SessionKey)

answered Oct 19 '12 at 08:58

John Woo

258,903
69
498
492

i am referring to column `SessionKey`. – John Woo Oct 19 '12 at 09:02
is its right setting index to fields slow down the insert operation? – Renjith K N Oct 19 '12 at 09:08
1

Yes of course it will, and in addition to that it will take some more disk space, too. (But it will most certainly be not that much and be worth it for sure). Depending on the amount of entries you might wait until nighttime or something to try that, because creating the Index may take some time (for one entry its not much, but for all that exist already .. who knows) – Flo Oct 19 '12 at 09:21

score 1 · Answer 2 · edited Jan 31 '13 at 20:25

Like others suggested, adding an Index would be the first and easiest thing to do. If you have tons and tons of lines in there, going through all of them might take some time anyways.

I once had a problem with something like this once, where someone coded a system, where users could vote on news-entries. Every vote was saved as a single line in the database. On every webpage there was a list of the "top voted" news. This basically meant there was a query to select the complete votes table, sum them up, and sort after that sum. With entries in the multiple 100k range, this was taking some serious time. Someone before me "solved" it, by trying to "cache" the results. This worked nice most of the time, but if you had cleaned all caches, then the whole page messes up for some hours until the caches was built again. I then fixed it by not saving every vote on an own row, but just sums for every entry.

What I want to tell you with this: You could try either caching (but the result wouldn be "live" of course), or change something in the database like adding a field or table where you store the count that you want to read which you update on every insert to the visits table. This would create a little more load on insert, but getting that number would be super cheap.

Mysql query optimization for Distinct count

2 Answers2