[SOLVED] Order BY in large databases

[f1r3fl3x]

Hi all, i have a problem. I have a very big database (with small row data). I use fulltext for the search. But i'm trying to do a top 50 page , and there is my  problem. When i use a normal query, the result loads about 15-20 seconds. How can i speed it up ?

 

Mysql Version: 5.1.30

 

Query:

SELECTid, artist, title FROM lyrics ORDER BY viewed DESC LIMIT 50

 

Table Structure:

CREATE TABLE IF NOT EXISTS `lyrics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `artist` varchar(255) DEFAULT 'no_artist_name',
  `title` varchar(255) DEFAULT 'no_song_name',
  `lyrics` text,
  `letter` varchar(255) DEFAULT 'no',
  `time` varchar(255) DEFAULT NULL,
  `search_key` varchar(255) DEFAULT NULL,
  `viewed` int(30) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `b` (`search_key`),
  FULLTEXT KEY `artist` (`artist`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `artist,title` (`artist`,`title`),
  FULLTEXT KEY `search_key` (`search_key`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=550857;

 

I've tried:

SELECT artist, title, id FROM lyrics WHERE MATCH(artist)AGAINST('a* b* c* d* e* f* g* h* i* j* k* l* m* n* o* p* q* r* s* t* u* v* w* x* y* z*' IN BOOLEAN MODE) AND viewed >= 1 LIMIT 50

(for now 1 is the biggest view number )

[gffg4574fghsDSGDGKJYM]

`viewed` int(30) DEFAULT '0',

is a little bit weird since INT datatype can't be display in more than 11 digits (with the - negative sign) should be :

`viewed` int(11) DEFAULT '0',

 

I'm not sure exactly what wrong with your query

but you can try adding a index to your viewed field and read this :

 

http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html

http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

[Yesideez]

Normalise your data.

 

You've got artist as a VARCHAR - I'd create a second table called artists and store all the data in there and link the two with artistid.

 

If anything else is duplicated I'd do the same with those as well.

 

Creating an index (as mentioned) for any data that gets searched on is going to help as well.

 

If any numeric field cannot be a negative value then change the type to UNSIGNED as this will double the capacity.

 

Eg.

TINYINT is -128 to +128

 

TINYINT UNSIGNED is 0 to 255.

[f1r3fl3x]

@Yesideez, the thing with the artist id, that exactly the way i'm doing it , and 10x both the index thing realy helped me