Re: Index problem - Sasha Pachev - com.mysql.lists.bugs

3 messages in com.mysql.lists.bugsRe: Index problem

From	Sent On	Attachments
Andreas Johansson	17 Aug 2001 06:21
Sinisa Milivojevic	17 Aug 2001 07:28
Sasha Pachev	17 Aug 2001 10:43

Subject:	Re: Index problem
From:	Sasha Pachev (sas...@mysql.com)
Date:	08/17/2001 10:43:10 AM
List:	com.mysql.lists.bugs

On Friday 17 August 2001 07:21, Andreas Johansson wrote:

Hi,

I've got and index bug/problem. I'm not sure that this is the correct forum to post too so please don't flame if it isn't. I'm using 3.23.38 on Linux (full info after the table spec).

I have a threaded forum application using a table (see table and index spec at the bottom). In the table I have 720.000 rows so I will spare you from posting the data.

I want to fetch all articles with id x or mother x (ie, the complete thread). This is however very slow. The reason is that MySQL wont use the indexes that it could. First I tried.

select * from articles where 1323922 in (id, mother)

which is according to me a nice way to write. MySQL doesn't know how to optimize this, I can understand that. Explain returns no possible keys (pk) and no used keys (uk).

Then I rewrote it simpler to optimize:

select * from articles where id = 1323922 or mother = 1323922

Pk then is PRIMARY,idxmother but uk still is nothing. Wouldn't it be easy to use both indexees and combine the results? If I split the query like:

select * from articles where id = 1323922 select * from articles where mother = 1323922

it works fine but I get some ugly code joining the results.

Bug, problem or just badly written sql by me?

MySQL can currenly use only one key per table instance in a join. To optimize a=A or b=B you need to be able to use two keys. So it is better to run four queries:

create temporary table temp_art type=heap select * from articles where id=1323922; insert into temp_art select * from articles where mother=1323922; select * from temp_art; drop table temp_art;

If your servers is running less than 1000 queries per second, the overhead of sending an extra query would be barely noticable.