Create Index ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Create Index ?

Post by anjanesh »

I just know the basics of MySQL and wanted to know what my client meant by saying

"on the table creation script, please add any appropriate indexes needed to optimize the reporting script speed. I do not want the db table to be bogged down by a large table scan when there are a lot of records in the table." ?

The first field (ID) is of type BIGINT and Primary.

Any idea how to create and Index in MySQL ? I know Index can be created in PostgreSQL but what do I have to do in MySQL ?

Thanks.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Thanks timvw. I've seen this but dont know the purpose of this fully.
Where can I get info on how Index reduces search time. All this time I thought index was just to Index a field. Now I read that it can be used to index part of a field for faster SELECT statements ?

Any ideas on this ?

Thanks
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Indexes can offer your DBMS a faster execution path..

Here is how MySQL uses them:
http://dev.mysql.com/doc/mysql/en/mysql-indexes.html..

If you search this forum you will find quite some posts of people witnessing that the query execution time was seriously reduced...

For example think about binary search in a sorted array.. This is how B-Tree index can be used to search values.. Same applies for other types of index.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Thanks for your link.

I have a table with 1 bigint field, 6 varchar fields and 1 datetime field.

The bigint is the ID field which is Primary. Isnt that an Index itself ?
How can I possibly add another Index to this table which will generate faster SELECT queries ?

Thanks
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Most DBMS will create a UNIQUE index on the Primary Key.

For example, if you have a lot of queries like

Code: Select all

SELECT *
FROM table
WHERE datetime > '2004-03-12 13:42:00';
In this case it's very likely MySQL will work faster if it has an index on the datetime column.. I believe there is an EXPLAIN instruction that will give you hints too :)
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I have the queries like this:

Code: Select all

$SQL_1 = "SELECT `varcharfield1`, COUNT(*) AS `Total`
          FROM `table1`
          WHERE
          UNIX_TIMESTAMP(Entry) - UNIX_TIMESTAMP('$FromDate') > 0 AND
          UNIX_TIMESTAMP('$ToDate') - UNIX_TIMESTAMP(Entry) > 0
          GROUP BY `varcharfield1`
          ";

$SQL_2 = "SELECT `varcharfield2`, COUNT(*) AS `Total`
          FROM `table1`
          WHERE
          UNIX_TIMESTAMP(Entry) - UNIX_TIMESTAMP('$FromDate') > 0 AND
          UNIX_TIMESTAMP('$ToDate') - UNIX_TIMESTAMP(Entry) > 0
          GROUP BY `varcharfield2`
          ";
If varcharfield1 and varcharfield2 are varchar(100), would it be better to Index varcharfield1 with size 10. Same for varcharfield2 or better to Index Entry (the datetime field) ?

Thanks
Last edited by anjanesh on Tue Aug 02, 2005 10:36 am, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You might want to use the EXPLAIN command, as MySQL will know better than me ;))

But you could move the UNIX_TIMESTAMP($date) to the other side of the comparison as it is a constant... And thus would save some calculations ;)

Code: Select all

UNIX_TIMESTAMP(Entry)  > UNIX_TIMESTAMP('$FromDate') AND
UNIX_TIMESTAMP('$ToDate')  > UNIX_TIMESTAMP(Entry)
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Those queries won't be helped by an index -- the column in question needs to be involved in join conditions or where conditions. If mysql allows "functional indexes" an index on UNIX_TIMESTAMP(Entry) would likely be useful.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Is the timestamp conversion strictly necessary? Why don't you change the input to match the format of whatever has been stored? You didn't give us the structure of your table, but I suspect that this is the solution.
Post Reply