Page 1 of 1
Create Index ?
Posted: Wed Jul 27, 2005 2:05 am
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.
Posted: Wed Jul 27, 2005 7:37 am
by timvw
Posted: Wed Jul 27, 2005 7:41 am
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
Posted: Wed Jul 27, 2005 8:03 am
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.
Posted: Wed Jul 27, 2005 1:41 pm
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
Posted: Wed Jul 27, 2005 1:58 pm
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

Posted: Wed Jul 27, 2005 2:10 pm
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
Posted: Wed Jul 27, 2005 2:22 pm
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)
Posted: Wed Jul 27, 2005 2:23 pm
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.
Posted: Wed Jul 27, 2005 3:37 pm
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.