Create Index ?
Moderator: General Moderators
Create Index ?
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.
"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.
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.
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.
Most DBMS will create a UNIQUE index on the Primary Key.
For example, if you have a lot of queries like
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 
For example, if you have a lot of queries like
Code: Select all
SELECT *
FROM table
WHERE datetime > '2004-03-12 13:42:00';I have the queries like this:
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
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`
";Thanks
Last edited by anjanesh on Tue Aug 02, 2005 10:36 am, edited 1 time in total.
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
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)- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US