MySQL Query Issue - Count Query Uses lot of CPU

Question

Hi,

We have a table on mysql to log all visitors of our site. The structure is shown below

    CREATE TABLE  `tblvisitors` (
      `visitorURL` longtext,
      `visitorDatetime` datetime DEFAULT NULL,
      `visitorIP` varchar(255) DEFAULT NULL,
      `visitorID` int(10) NOT NULL AUTO_INCREMENT,
      `visitorUser` varchar(255) DEFAULT NULL,
      `visitorShow` varchar(50) DEFAULT NULL,
      `visitorIPcaption` varchar(255) DEFAULT NULL,
      `visitorIPRange` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`visitorID`),
      KEY `INDEXDT` (`visitorDatetime`),
      KEY `INDEXIP` (`visitorIP`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table has right now more than 5 million records.

We have an INSERT operation every half second and also a SELECT operation every half second which involves a CRITERIA on visitorURL column and Select of COUNT field.

The query is :

    SELECT COUNT(visitorURL) FROM tblVisitors 
        WHERE visitorURL='http://mihirdarji.com/something'

This shoots up the CPU usage to 90% in an 8 core server with 8 GB Ram.

The MySQL admin shows lot of connection with above query waiting to be executed.

Any suggestions would be welcome.

Explain plan says

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tblVIsitors | ALL | NULL | NULL | NULL | NULL | 4293277 | Using where |

Answer 1

Do you have any indexes defined in this table? If not, you should definitely make an index on the visitorURL column. Your current query requires full table scan and this takes a lot of time. However, indexing a long text string may give you the speed improvement you need if you use a simple index (because the number of unique strings is similar to record count in table). So, a full text index could be a better choice in this case. This will require you to use the special "full-text search functions".

Answer 2

Does visitorIp really need to be varchar(255) ?

Answer 3

1. first create an index on visitorURL
2. second only COUNT something static SELECT COUNT(1) FROM tblVisitors WHERE visitorURL=...

Answer 4

run explain on this query

Answer 5

It seems that enabling option option_mysqld_low-priority-updates did the trick for me.

Answer 6

rather than indexing the URL column, you could store a hash of the URL in a separate varchar column and index and query that. But whatever you do, you'll need an index of the column you're counting. Also look at the MyIsam keycache settings to ensure the indexes are handled as effectively as possible.