Welcome to WebmasterWorld Guest from 54.242.94.72

Forum Moderators: open

Message Too Old, No Replies

Tricky Question about a SIMPLE query

mysql sql query optimisation index database

     

iamvela

8:44 pm on Mar 12, 2009 (gmt 0)

5+ Year Member



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

8:56 pm on Mar 12, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

9:22 pm on Mar 12, 2009 (gmt 0)

5+ Year Member



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

9:36 pm on Mar 12, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

9:49 pm on Mar 12, 2009 (gmt 0)

5+ Year Member



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

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

LifeinAsia

10:06 pm on Mar 12, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

11:40 pm on Mar 12, 2009 (gmt 0)

5+ Year Member



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

LifeinAsia

11:55 pm on Mar 12, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month