The Cache: Technology Expert's Forum
Welcome, Guest. Please login or register. September 16, 2019, 01:23:08 PM

Login with username, password and session length

Pages: [1]
Author Topic: MySQL performance with text based order by  (Read 1473 times)
Offline Offline

Posts: 1153

paha arkkitehti

View Profile
« on: November 15, 2009, 05:34:23 AM »

I'm doing simple query like this:
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:
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 Cheesy Well... live and learn..
« Last Edit: November 15, 2009, 05:38:41 AM by kurdt » Logged

I met god and he had nothing to say to me.
Olde World Hacker
Offline Offline

Posts: 10096

View Profile
« Reply #1 on: November 15, 2009, 12:14:18 PM »

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.

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks

Valid XHTML 1.0! Valid CSS!