Re: Questions about indexing

[Chris Nolan]

Even if your database fits entirely in memory, not having indexes in 
place would not be a good idea.

In an interview Monty did regarding in-memory databases, he very 
specifically made the point that where
your database is sitting will never remove the need for various types of 
index.

From some extremely boring computer science/software engineering 
subject I did a few years back:

1. If there's no index to play with, you'll need to do a full table scan 
to find the rows matching the
conditions predicated in your WHERE clause.
2. If there is an index or two to play with, you'll at the very least 
cut down the number of rows that
will be read from disk for further inspection should the optimiser 
choose to use them (look up FORCE INDEX(...)
in MySQL)
3. For B-Tree and B+Tree indexes, you're looking at an average of 1-2 
probes (with a probe being roughly
the same expense I/O-wise as reading a row) for finding an item matching 
an "=" condition and the same for
finding the beginning/end of a range for range-style queries (eg: ... 
WHERE a > 20 AND a < 40).

Hope this helps!

Regards,

Chris

Joshua Thomas wrote:

Find out the real way: Use EXPLAIN and BENCHMARK() commands to get the speed
of your operations with and without an index. That's the only way to know
for certain.
Cheers,

Joshua Thomas
Network Operations Engineer
PowerOne Media, Inc.
tel: 518-687-6143
[EMAIL PROTECTED] 

---
In theory there is no difference between theory and practice. In practice
there is. 
- Yogi Berra 
---



 

-----Original Message-----
From: Dan Anderson [mailto:[EMAIL PROTECTED]
Sent: Monday, December 15, 2003 1:18 PM
To: Chris Elsworth
Cc: [EMAIL PROTECTED]
Subject: Re: Questions about indexing
   

With such a small database it really boils down to just being tidy;
you don't want indexes you're not going to use.
     

Well the database is going to be like 200MB and executing several
hundred queries a minute.  Thus my concern about speed.  Is a 
P4 w/ 1GB
RAM going to choke and die, or will indexing help out?

Thanks in advance,

Dan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]

   

 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]