473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Used EXPLAIN, have indexes - query still surprisingly slow

I have the following query that takes anywhere from 1 to 3 seconds
to run. I would expect it to run in less than 1/2 a second (and
I really need it to do so). I've added the appropriate indices
and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere.

SELECT message.id
FROM message, message_thread
WHERE message.id = message_thread.message_id
AND message_thread.thread_id = SOME_CONSTANT_NUMBER

where SOME_CONSTANT_NUMBER is an integer literal, like 16400 (and no,
I'm not putting quotes around the number).

Here are the two tables involved:

message table
------------------------------------------------------------------
Field Type NULL Key Default Extra
------------------------------------------------------------------
id int(11) PRI NULL auto_increment
msgtype_id int(11) MUL NULL
content blob YES
precedence varchar(255) YES
message_thread table
------------------------------------------------------------------
Field Type NULL Key Default Extra
------------------------------------------------------------------
id int(11) PRI NULL auto_increment
message_id int(11) MUL -1
thread_id int(11) MUL -1
contact varchar(255) YES

The message table has 1,117,213 records and the
message_thread table has 2,563,893 records. At most, each query
will return 200 records.

Here is the output of the explain function (turned sideways):

---------------------------------------------------------------
table : message_thread message
---------------------------------------------------------------
type : ref eq_ref
---------------------------------------------------------------
possible_keys : thread_id, PRIMARY
message_id
---------------------------------------------------------------
key : thread_id PRIMARY
---------------------------------------------------------------
key_len : 4 4
---------------------------------------------------------------
ref : const message_thread.message_index
---------------------------------------------------------------
rows : 200 1
---------------------------------------------------------------
Extra : Using where Using index
---------------------------------------------------------------
This is running on a 2.2 GHz Pentium 4, NTFS File system,
MS Windows 2000, MySQL 4.0.18. Each table is an InnoDB type.

I've tried a combined thread_id/message_id index in the message_thread
table; but, that did not seem to help at all.

Any help would be appreciated.
Jul 20 '05 #1
0 1063

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Christian | last post by:
HI, I have a function that is used to constrain a query: Select COl1, Col2 From MyTable WHERE col1 = ... AND col2 = ... And MyFunction(col1) = ... My problem is that MyFunction is executed...
0
by: Jacob Larsen | last post by:
We can get a slow query log with log-long-format and even a summarized version with mysqldumpslow. But why don't we have a script that can parse the slow query log and produce sql statements...
3
by: Mark Harrison | last post by:
I have indexed two columns in a table. Can somebody explain to me why the first query below uses an Index Scan while the second uses a Seq Scan? Many TIA! Mark planb=# \d abcs Table...
5
by: radhika | last post by:
ENVIRONMENT: DB2 OS/390 V6 Peoplesoft v8 I have a strange situation: I have a table called TABLEA and it has indexes Ind1 Ind2 Ind3
1
by: Waldo Nell | last post by:
Hi, I have a *huge* problem. I have a table with indexes on but the moment I have an OR in my SELECT query it seems to not use the appropriate index. oasis=> \d purchases Table...
6
by: Dan Pelleg | last post by:
I'm trying to access a table with about 120M rows. It's a vertical version of a table with 360 or so columns. The new columns are: original item col, original item row, and the value. I created...
1
by: pootlecat | last post by:
Hello everyone, I have a fair sized table now (1,955,041 rows) and it currently has two indexes: PRIMARY is the ID number and Keywords is a FULLTEXT index of the Keywords column (Text). ...
22
by: Zamdrist | last post by:
I am tasked with maintaining a large database still on SQL Server 7.0. Performance is an issue not surprisingly and I've targeted two rather large tables to see what I can do with the indexes. ...
3
by: skaushik | last post by:
Hi all, I ran an explain plan for a sql query which has some bind variables used in it. The plan shows that index is being used but the execution takes a very long time. Also, in another query the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.