# Doubt about indexing

**URL:** <https://forums.percona.com/t/doubt-about-indexing/1826>\
**Category:** Other MySQL® Questions\
**Created:** [June 8, 2012, 6:39am UTC](https://forums.percona.com/t/doubt-about-indexing/1826 "2012-06-08T06:39:37Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sgmartinez](https://avatars.discourse-cdn.com/v4/letter/s/839c29/32.png) [@sgmartinez](https://forums.percona.com/u/sgmartinez)\
**Post date:** [June 8, 2012, 6:39am UTC](https://forums.percona.com/t/doubt-about-indexing/1826/1 "2012-06-08T06:39:37Z")

</div>

Hi,

I would like to ask a question regarding the way Percona Server does the indexing when using its XtraDB engine.

I have a table with this structure:

CREATE TABLE `event` (  
 `id` binary(16) NOT NULL,  
 `agent_ctx` binary(16) NOT NULL,  
 `timestamp` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,  
 `tzone` float NOT NULL DEFAULT ‘0’,  
 `sensor_id` binary(16) DEFAULT NULL,  
 `interface` varchar(32) NOT NULL,  
[…]  
 `src_host` binary(16) DEFAULT NULL,  
 `dst_host` binary(16) DEFAULT NULL,  
 `src_net` binary(16) DEFAULT NULL,  
 `dst_net` binary(16) DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `event_idx` (`timestamp`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The table has some rows stored:

mysql\> select count(_) from event;  
±---------+  
| count(_) |  
±---------+  
| 5863 |  
±---------+  
1 row in set (0.00 sec)

If I select rows with a timestmap \> ‘2012-06-06 14:17:40’ some rows are retrieved:

mysql\> SELECT count(_) FROM event b WHERE b.timestamp \> ‘2012-06-06 14:17:40’;  
±---------+  
| count(_) |  
±---------+  
| 18 |  
±---------+  
1 row in set (0.00 sec)

And if I select rows with a timestmap \< ‘2012-06-06 14:17:40’ some rows are retrieved too:

SELECT count(_) FROM event b WHERE b.timestamp \< ‘2012-06-06 14:17:40’;  
±---------+  
| count(_) |  
±---------+  
| 5843 |  
±---------+  
1 row in set (0.00 sec)

But when running the explain command this queries seem to be different although I suppose that they could use the same index. Why timestamp index isn’t used automatically in the second query?

[B]mysql\> explain SELECT \* FROM event b WHERE b.timestamp \> ‘2012-06-06 14:17:40’;  
±—±------------±------±------±--------------±------- —±--------±-----±-----±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±------±--------------±------- —±--------±-----±-----±------------+  
| 1 | SIMPLE | b | range | event\_idx | event\_idx | 4 | NULL | 18 | Using where |  
±—±------------±------±------±--------------±------- —±--------±-----±-----±------------+  
1 row in set (0.00 sec)

mysql\> explain SELECT \* FROM event b WHERE b.timestamp \< ‘2012-06-06 14:17:40’;  
±—±------------±------±-----±--------------±-----±- -------±-----±-----±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±-----±--------------±-----±- -------±-----±-----±------------+  
| 1 | SIMPLE | b | ALL | event\_idx | NULL | NULL | NULL | 5364 | Using where |  
±—±------------±------±-----±--------------±-----±- -------±-----±-----±------------+  
1 row in set (0.00 sec)[/B]

At the end what I would like to do is deleting rows of this table according their timestamp, but I can’t force index use in a delete SQL instruction. How can I use the timestamp index automatically if I have to delete rows that match a condition like “b.timestamp \< ‘2012-06-06 14:17:40’”?

Thanks in advance!!

---

<div class="post-metadata">

**Author:** ![perforum111](https://avatars.discourse-cdn.com/v4/letter/p/aca169/32.png) [@perforum111](https://forums.percona.com/u/perforum111)\
**Post date:** [June 11, 2012, 1:49pm UTC](https://forums.percona.com/t/doubt-about-indexing/1826/2 "2012-06-11T13:49:56Z")

</div>

I opened a similar topic few days ago  
http:// [Percona Community Forum - Technical forum for help with Percona's open source software for MySQL®, PostgreSQL, and MongoDB® databases.](http://forum.percona.com/index.php?t=msg&th=2487&start=0&am) p;  
the workaround that works for me is casting string as datetime (examples in topic)…maybe in your case casting it as timestamp