[RESOLVED] Indexing question

[Dolemite50]

Hi,

I've read that it's good to index on anything you might target in a search and I've also read that you shouldn't go to index crazy because of speed. I'm curious to hear how some of you would go about this: I have a table called SONGS with 6 columns:

songid - MEDIUMINT(6)  PRI-Key
bandid - MEDIUMINT(6) 
song_title - VARCHAR(40) 
genre_id - TINYINT(2)
song_date - DATETIME
exclusive_track - TINYINT(1)

I currently only have songid as a PRI key. In a related table called BANDS I have the bandid as the PRI key. Should I be defining a reference somehow? I've read up on foreign keys but it looks like it involves changing DB types which I'm a little nervous about.

Also, If I often query by "WHERE genre_id = 6" or "WHERE exclusive_track= 1" or "ORDER BY song_date" ...is it a good idea to make them an index too?

Lastly, since no band will have the same song title twice, do I have anything to benefit from creating a unique index on those two columns?

Thanks, any advice is much appreciated.

[trexx]

let mysql explain what it's doing while executing your query. e.g.:

explain select 1 from my_table;

with that you know exactly where you need an index and where you don't. the mysql documentation has some good advice on how to interpret the output of the explain command. http://dev.mysql.com/doc/mysql/en/explain.html

[Dolemite50]

Hi trexx,

Thank you very much for the feedback.

It was actually using "explain" that brought me to make this post. I've been studying it but I'm still uncertain on a lot of things. I don't have any benchmarking tools so I pretty much just go by rowcount on the explain statement to gauge how I'm doing. If I query by the pri key it will usually be one row. I am wondering if it is an increase in speed to have it find the same using the unique Songid+Song_title index. They would each be one row but is the combo more "blatant" and therefore faster?

I guess my most important question is the first one regarding how to "relate" common fields. If I have a SONG table with SONGID as PRI and it also contains BANDID which is PRI in the BAND table, is there a general rule to consider since they will be referring to each other back and forth so much? I've been using queries like:

 
SELECT s.song_title, g.genre,  b.bandname from song s, band b, genres g WHERE s.songid='$songid' AND s.bandid=b.bandid AND s.genre_id=g.genre_id

Is that an instance where a foreign key would come in handy? If so, is there an alternative when using a MyISAM table? When you see a query like that do you automatically think "Hmm,..that looks like a good time to use ______.
INNER JOIN/UNION/FOREIGN KEYS/HAVING/OUTER LEFT INNERMOST RIGHT CROSS GROUP JOIN?

Sorry, I've been crash-coursing normalization for weeks and it's all starting to blur. :eek: 😕 :queasy:

[trexx]

Yes, if you join tables it is a good idea to have an index on the foreign key. I always use myISAM tables , so i can't give you any alternatives =)

You don't need a benchmark tool to measure the time it takes to execute a query. Php and mysql will do. Just include the code below before and after you execute your query (replace the constants for the connection parameters). But you really need a LOT of data to notice a difference g

Your query looks ok. there's no difference between the way you do it and using the INNER JOIN notation.

This link here describes quite good when and why you use an index:
http://dev.xoops.org/modules/phpwiki/index.php/MySQLIndexes

debug-begin.php:

<?php
//-----------------------------------------------------
// FUNCTION getMicroDiff
//-----------------------------------------------------
function getMicroDiff($beg, $end)
{
	$aBeg = split(" ", $beg);
	$aEnd = split(" ", $end);

$sec  = $aEnd[1] - $aBeg[1];
$time = ($sec + $aEnd[0]) - $aBeg[0];
return $time;
}


//----------------------------------------------------------------
// FUNCTION getQueryCount
//----------------------------------------------------------------
function getQueryCount()
{
	mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
	mysql_selectdb(DB_FILE);	
	$rs = mysql_query('SHOW STATUS');
	while($row = mysql_fetch_assoc($rs))
		if($row['Variable_name'] == 'Questions')
			return($row['Value']-1);

return 0;
}

$timeBegin = microtime();
$queryBegin = getQueryCount();
?>

debug-end.php:

<?php
$timeEnd = microtime();
$queryEnd = getQueryCount();
$queryTotal = $queryEnd - $queryBegin;
?>
<hr>
<h2>Debug Information</h2>
<pre>
<table width="100%" cellpadding="2" cellspacing="2" border="1">
	<colgroup>
		<col width="30%">
		<col width="70%">
	</colgroup>
	<tr>
		<td><b>Rendering time</b></td>
		<td><?=getMicroDiff($timeBegin, $timeEnd)?> seconds</td>
	</tr>
	<tr>
		<td><b>Queries executed</b></td>
		<td><?=$queryTotal?> queries</td>
	</tr>
</table>
</pre>

[Dolemite50]

Bless you trexx!

That was extremely helpful, thanks a lot.

Kyle