next »

[kurdt]

I'm doing simple query like this:

Code:

SELECT * FROM (`itembase`) WHERE `ranking` <= '10' ORDER BY `name` LIMIT 35055, 25

but it performs like shit. When I mean shit I mean I delete the process after it's been sending data for 300 seconds. Then query like this:

Code:

SELECT * FROM (`itembase`) WHERE `ranking` <= '10' ORDER BY `name` LIMIT 75, 25

is lightning fast.

I created index for 'name' but it's still very slow. But if I change ordering to 'ranking' for example it's really fast. So my conclusion is that the performance goes to shit when order by is with text even with index.

How can I improve the performance if I absolutely must order by 'name'? I'm not very familiar with different options with indexes so is there something specific I should be using with text?

*edit* After quick experiment it seems that you shouldn't use INDEX with text but FULLTEXT Well... live and learn..

[perkiset]

Yes ... FULLTEXT on a text field is the right way to go. But does NAME really need to be TEXT? Should that actually be a varchar or char? That would really increase the speed as well. TEXT fields, especially when you think they should be FULLTEXT indexed should not be used like this ... it'll bog on you badly.

But the real issue (I think) might be that you're not indexed on ranking... you might consider changing that index to ranking + name, so that you'll get a high level of granularity and both sides of that query are indexed. That's where I'd start. I'd not accept anything less than sub-second queries. Use EXPLAIN to see what MySQL is trying to do as well.