MySQl query taking too much time

[jessie]

Here is my MySQL query..it takes a lot of time to execute and show results. the results is the content of a set of 24000 articles.

SELECT Content FROM AQUA_MASTER WHERE Article_ID IN (SELECT Article_ID FROM AQUA_TAGGING WHERE Tag_ID=1736);

why is that? can somebody tell me how i can solve this problem?

jessie

[Tolls]

First off is there an index on Tag_ID?
Then is there an index on Article_ID?

How big are the two tables involved?

Finally, what classes as a long time?

[jessie]

no.there are no indexes on either. the tables are too big ,containing around 800,00 rows each. to execute this query it takes around 5 minutes...

[Tolls]

Add indexes.

Without them MySQL has to trawl every row of the Aqua_Tagging table to check the tag_ID field. That's a huge amount of unecessary work.
Once it's done that, it then has to trawl the Aqua_Master table from beginning to end for each result returned in the first part to find the articles.

[jessie]

the indexes are created...but still it is slow....what could be the reason?

[Tolls]

And how long does it take now?

Also, how are you timing it?

ETA: WHile we're at it, what is the Content field?

[jessie]

the time appears in the sqlyog interface. it takes around 2 min and 10 seconds now... but still its late...the content field contains text of articles...

[Tolls]

So they're long text fields?
What happens if you select a really small field (like Article_id)?
I'm just wondering if it's the volume of data....then again, SQLyog only transfers about 50-100 rows on a query, but it still might be the problem.

Other than that you'll probably want to look at what the execution plan is for the query. Can't help you on how to do that, but the MySQL docs should have the info. The plan should show whether it is using both indexes.

[Tolls]

Here you go.
Explain.

So you'll want to run:
EXPLAIN SELECT Content FROM AQUA_MASTER WHERE Article_ID IN (SELECT Article_ID FROM AQUA_TAGGING WHERE Tag_ID=1736)

possibly with the EXTENDED keyword as well.

As for interpreting the results...:)

[jessie]

it luks like this.

EXPLAIN EXTENDED RESULT i dont understand it..does it use the indexes?

[Tolls]

The subquery is, the main query isn't.
Actually the index on tagId is unecessary since it appears to be the primary key, so is automatically indexed.

Are you sure there's an index on Article_ID?
Do a simple query on Aqua_master where article_id = some number and see what the explain gives for that.

[Tolls]

Curious.
Testing against a table I have here (names changed to protect the innocent):

select *
from some_table
where some_indexed_field = 1

uses the index.
As does:

select *
from some_table
where some_indexed_field in (1,2,3)

But this does not:

select *
from some_table
where some_indexed_field in (select some_other_indexed_field from some_other_table)

The subquery index is used, but the main query one is not.

At this point I can only suggest going to a MySQL forum.

[Tolls]

OK, there is a problem with IN and subqueries in MySQL, and has been for years.

Use a JOIN:

SELECT Content
FROM AQUA_MASTER am, AQUA_TAGGING at
WHERE am.Article_ID = at.Article_ID
  AND at.Tag_ID=1736

ETA: Thinking about it I should have suggested that in the first place (along with the index stuff).

[masijade]

Correct. It doesn't, because it doesn't know, beforehand, what those values are.

Try doing

select some_table.*
from some_table, some_other_table
where some_table.some_indexed_field = some_other_table.some_other_indexed_field
   and some_other_table.some_other_confining_field = 'some_value'

or

SELECT some_table.* FROM some_table LEFT JOIN (some_other_table)
    ON (some_table.some_indexed_field = some_other_table.some_other_indexed_field
          and some_other_table.some_other_confining_field = 'some_value')
)

Edit: Far too slow. Also, that problem exists in most DB's since they cannot get a "handle" on the values and so cannot create their action plan properly (which the action plan for an IN is determined differently than for a join, of course).

[Tolls]

Yep.
And I never encounter it because I don't write my queries like that...
One of those things I never end up thinking about.

[jessie]

hi tolls,

the query
SELECT Content
FROM AQUA_MASTER am, AQUA_TAGGING at
WHERE am.Article_ID = at.Article_ID
AND at.Tag_ID=1736

works in 23 seconds..the others took a lot of time still...but anyway i think 23 seconds is much better. thnks guys for your help..... :)

[FON]

works in 23 seconds..

I wish i could participate in this thread earlier...
One thing is sure - you got yourself good optimization lesson :)

Do you have to run this query in runtime?
If not, simply prepare it earlier, place it in some help table and use SELECT when you need it. This is common practice for report creation.

Maybe you can play with your server configuration and
try to improve this. I had positive experience changing few config params while working on Postgres DB.
As for MySQL, in doc i found 'key_buffer_size' and 'table_cache' as performance related. Try them.

There are not many columns so using VIEWS is not an option here, but can you do any 'horizontal partitioning' and split big tables and use union on them when needed?
Or use 'vertical partitioning' and create this tables with small number of columns and use additional tables to store remaining columns.

Perfect time for you to play, explore and learn!