mySql Indexes to Optimize Applications

[blackcell]

I can create indexes but, how do you utilize them to optimize look-ups? Is it something that happens behind the scenes or do you have to tailor your queries to utilize them?

 

<?php
$sqlQuery = "SELECT * FROM `sqlTable` WHERE `userDepartment` = 'ENG'";
?>

Would I need to modify the example query above to optimize the look-up when there are 5000+ records?

 

Thanks for any help.

[Barand]

create an index on usedDepartment if you don't have one already. No change needed for the query, MySQL should use it all by itself.

[blackcell]

So by creating an index you are allowing mysql to look at the table from a different(more efficient) angle?  This this is true, why not just have a built in mechanism for mysql that will create an index for each field when created?

[DarkWater]

Because it takes up hard disk space.

[blackcell]

I see...

I found something interesting and I have a theory as to why it happens.

I have a report that will query all records with field1 containing the char Y.

The possibilities for this field are Y or N.

This sql query takes FOREVER to run:

<?php
$sqlQuery = "SELECT * FROM `table` WHERE `field1` != 'N'";
?>

This sql query takes 20 seconds to rip through 4000+ records:

<?php
$sqlQuery = "SELECT * FROM `table` WHERE `field1` = 'Y'";
?>

I have an index on field1

 

Why is there such a time difference? Is it because when looking for != 'N' it leaves the possibility for everything else where looking for ='Y' will know what record to start on the index and rule all other possibilities out?

 

[DarkWater]

I'm not too sure, but I think it probably has to do with how MySQL handles comparisons.  Maybe for != it assembles all the records and takes out the ones where it's != 'N', which can take longer.

[Barand]

I don't know your table structure but 20 seconds ain't exactly ripping.

SELECT COUNT(*) FROM sales;
+----------+
| COUNT(*) |
+----------+
|    10917 |
+----------+

SELECT * FROM sales WHERE prodcode <> '610';
10395 rows in set (0.13 sec)

SELECT * FROM sales WHERE prodcode = '610';
522 rows in set (0.02 sec)