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]
  Print  
Author Topic: MySQL performance with text based order by  (Read 1473 times)
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


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

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 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.
perkiset
Olde World Hacker
Administrator
Lifer
*****
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.
Logged

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]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
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!