help me with slow load times for a large database

[scarhand]

i have a database with around 250,000 entries in a table

 

the problem is, pulling data from this table seems to take a REALLY long time

 

i want to know how i can make the queries execute faster

 

the web site is at www.fretlords.com

 

as you can see if you go to a letter, the page loads slow

 

here is the table structure:

 

  id int(10)         

  ugid int(10)          

  band text          

  slug_band text             

  song text            

  slug_song text              

  tab longtext                

  version int(11)               

  date_posted int(10)                

  rating int(11)           

  views int(10)            

 

here is an example of a query:

 

$letter = 'a';

"SELECT DISTINCT(band), slug_band, COUNT(band) AS count FROM tabs WHERE band REGEXP '^$letter' GROUP BY band ORDER BY band ASC LIMIT 100";

[xtopolis]

Use indexes.  I'm guessing by the looks at it that it will check EVERY record to see if it matches "starts with $letter" which is very costly in itself.  Not sure about the GROUP BY either...esp with the distinct there..

 

Also, if I'm correct in assuming that COUNT(band) returns the same value for every row, meaning it counts all the distinct bands in the database, then it is also unnecessary; you could use a mysql_num_rows() to get that value, but I doubt that the count is high costly.

 

Also, it doesn't look like your database is normalized.  Have you read up on proper normalization?

[fenway]

That, and DISTINCT is not a function!

[scarhand]

That, and DISTINCT is not a function!

 

how would you write that query?

[fenway]

First, show us the EXPLAIN output of your existing query.