MySQL optimization guru needed

Anon

I have a fairly large table (up to 100,000 rows) that I need to display systematically.

Basically, I need to show 50 entries per page up to any unlimited number of pages.

here's the core of my current primary query

SELECT * from table1 WHERE value<5 ORDER BY id1 DESC, id4 LIMIT 50

It can also have different ORDER BY criteria, depending on how the user wants it displayed.

ORDER BY id1 DESC, id4 (default)
ORDER BY id2, id4 (common)
ORDER BY id3, id4 (infrequent)

I have an index on id1 on id2 and on id3. id4 has a relatively small range and wouldn't benefit to index anyway.

My problem is that even tho I am not indexing ID4, MySQL simply REFUSES to index on id1 when I try to sort it DESC . This is a documented "feature" and is destroying my ability to optimize the query.

ORDER BY id1 runs in 0.01 seconds
ORDER BY id1 DESC runs in 1.55 seconds

But without re-writing all the data, I HAVE to sort one column ascending and one column descending. If I can't do that, it would be necessary to write code to restructure the data itself. I don't want to do that if I can avoid it.

Any hints?

Thanks,
Eric

van__

$stmt = "SELECT * from table1 WHERE value<5 ORDER BY id1 DESC, id4 LIMIT $offSet,50";

Be sure to create an offset indicator.
Here's a small snippit from my library to do that for you....

<?
function getResultPages($resultCount,$resultLimit) {

// next we need to do the links to other results //
if ($resultCount > $resultLimit) {

// calculate number of pages needing links //
$numPages = intval($resultCount/$resultLimit);

/
$numPages now contains amount of pages needed.
Unless there is a remainder from the division
so we check for that next...
/

	if ($resultCount%$resultLimit) { // has remainder so add one page
	$numPages++;
	}

for ($i=1; $i<=$numPages; $i++) { 
/*
	Here we loop thru results and create 
	an array of pages and offsets to pass back
 */
$pageArray[$i]['OFFSET_PAGE_LINK'] = $resultLimit * ($i-1);
}

} else {
// then we had no extra pages so we pass a 1 element null value array
$pageArray[0]['OFFSET_PAGE_LINK']='';
}

return $pageArray;
}

Just pass the num_rows to result count and your desired limit of items per page (50 from your example) and then it will return an array of page links, which you may want to modify, since I set that array up for my own special uses 🙂

[deleted]

"id4 has a relatively small range and wouldn't benefit to index anyway."

How do you figure that? There are still 100k rows to read for any information about id4.

"Any hints?"

Well, switching to PostgreSQL is a good start.

Is this the query that's taking 1.55 seconds?
"ORDER BY id1 DESC, id4"

in that case I can imagine that mysql refuses to use an index for id1 because it has to do a sequencial scan for id4 anyway, so the index of id1 would not help.

[deleted]

How does that explain mysql not using the id1 index?

van__

Ooops, sorry... guess I messed up on that.

Good advice you gave though, switch to postGres.

Hahahahah!
That's great... "Well gee, let me make a plug for another database" You should go apply at Oracle and see if you can get paid to sit on message boards trying to tempt hapless users into purchasing 8i.

[deleted]

Keep your shirt on dude.

I take it you mis-interpreted my reply as "answer the damn question" instead of "I don't understand how that should increase the query speed, please explain."

Anon

<i> in that case I can imagine that mysql refuses to use an index for id1 because it has to do a sequencial scan for id4 anyway, so the index of id1 would not help.</i>

bzzzzzt

wrong. :-)

The values of id4 range from 1-4
If you know how they're stored, you'll realize that indexing it would crate a mssive 4-item B-tree which would be almost useless.

You'd also realize that once you'd isolated the results of a search through id1, that using a b-tree from id4 (over the entire result set) would actually SLOW DOWN the process.

My question more pertains as to WHY MySQL REFUSES to use the index on id1 when I do the ORDER BY id1 DESC, id4 and if there is a way around it.

If I do: ORDER BY id1 DESC, id4 DESC, the query is indexed and works great, but when I do it the other way, it takes several seconds.

Thanks,
Eric

Anon

I know how to do paging, thanks.

The recodset contains > 100,000 rows right now and I need to make these queries FAST, which means using an INDEX in combination with the LIMIT.

Some of the queries do not use an index.

What do I do?

That is my question.

Thanks,
Eric

[deleted]

"The values of id4 range from 1-4
If you know how they're stored, you'll realize that indexing it would crate a mssive 4-item B-tree which would be almost useless"

debatable. 🙂
Remember, you're not just selecting 50 records, you're ordering all records where value<5 to finally return only 50. You may inadvertently be causing a near-sequencial scan.

Anyway, back to why MYSQL doesn't use an index.
It is indeed a 'documented feature', mysql state in their manual that they can't use an index for mixed ASC and DESC ordering on parts of the same key.

I'm not sure why this affects your query, because you don't have an index on id4, so there's no mixed ordering on parts of the same key.

As for the speed, did you check for sufficient sort-bufferspace etc?

A forum, a FAQ, what else do you need?