Tricky Question about a SIMPLE query

mysql sql query optimisation index database

[iamvela]

I have a strange problem:

I have a query such as

SELECT h.id AS id, h.name AS name, hl.id AS hl_id, hl.date AS date
FROM (table1 AS h, table2 AS hl)
WHERE hl.id = h.id
ORDER BY date DESC LIMIT 100

This query takes about 0.08seconds

when I repeat the same query such as

SELECT h.id AS id, h.name AS name, hl.id AS hl_id, hl.date AS date
FROM (table1 AS h, table2 AS hl)
WHERE hl.id = h.id
ORDER BY id DESC LIMIT 100

This query takes as much as 28 to 49 seconds.

In this second example I am ordering on table1.id

Noteworthy:
1. both the table.id and table2.date are indexs (respectively)
2. Each table has almost the same number of records.

Why is this query taking so much longer?
--

[LifeinAsia]

I don't know if it will make a difference, but try:
SELECT h.id AS id, h.name AS name, hl.id AS hl_id, hl.date AS date
FROM (table1 AS h, table2 AS hl)
WHERE hl.id = h.id
ORDER BY h.id DESC LIMIT 100

Make sure that table2.id is indexed as well.

Are both table1.id and table2.id INT fields?

[iamvela]

I already tried that :)

Both of these: id and date are int(11)
id is autoincrement (the slower query)
date is unix date (faster query)

Now what?
--

[LifeinAsia]

Huh? You're contradicting yourself- are the "date" fields INT or DATE data types? (It's also easier if you don't name your fields reserved words like "date.")

[iamvela]

I know abt reserved words ;) but have legacy code too.

Anyways, like I said before date field is int(11)
--

[LifeinAsia]

And you also said

date is unix date (faster query)

Just trying to clarify.

[edited by: LifeinAsia at 10:06 pm (utc) on Mar. 12, 2009]

[iamvela]

Sorry didn't mean to add any confusion it is an int(11)

[LifeinAsia]

So, what you are saying is:
table1.id- INT (autoincrement)
table1.date- INT
table2.id- INT (autoincrement)
table2.date- INT

All 4 fields are indexed.

If so, you could try re-indexing both tables.