Best Practice for Processing dbase Data

[eluser]brianw1975[/eluser]
This is where extending out your MySQL admin abilities and understanding how basic indexes and advanced indexes can be used to increase your database response times.

The first way will probably be faster overall if you set an index on 'active' in that table.

The same if you set and index on 'Title' for the second query

Ditto for an index on 'active' and 'type'

but you don't want to go making indexes on every column or every combination of columns, it increases the memory footprint of the DB and any indexes aren't used they are basically taking up valuable RAM.

Basically, if you have queries that get used on say more than 20% of your page views, analyze those queries and determine which index are needed and which can speed up the DB response and ultimately the page load time -- esp if those queries have "where this = that and the_other = another and yada = dada" you might want to consider making an index based on 'this', 'the_other', and 'yada'

I run a website that has a single table with about 175megs of data - just one table, it needs some severe normalization, but with the use of indexes I was able to increase the response time drastically on most pages that requested data from that table.

Here's some info on that table:

Code:

Space usage    
Type      Usage
Data     130,990     KiB
Index     45,779     KiB
Overhead     782,628     B
Effective     176,004     KiB
Total     176,769     KiB

As you can see, the Index file alone is 45 megs.

After reading this post (I suggest you read it) here I'll be reviewing my indexes to reduce that usage.

Single queries to the database with good indexes will be faster than a single query and a lot of processing.