homepage Welcome to WebmasterWorld Guest from 54.166.122.65
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Tricky Question about a SIMPLE query
mysql sql query optimisation index database
iamvela

5+ Year Member



 
Msg#: 3869100 posted 8:44 pm on Mar 12, 2009 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3869100 posted 8:56 pm on Mar 12, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 3869100 posted 9:22 pm on Mar 12, 2009 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3869100 posted 9:36 pm on Mar 12, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 3869100 posted 9:49 pm on Mar 12, 2009 (gmt 0)

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

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

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3869100 posted 10:06 pm on Mar 12, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 3869100 posted 11:40 pm on Mar 12, 2009 (gmt 0)

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

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3869100 posted 11:55 pm on Mar 12, 2009 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved