SQL Database Optimization

batman1

New Member
Messages
92
Reaction score
0
Points
0
Should I add and INDEX onto a date "Y-m-d" field, even though many records can have the same date.

I run a query that check all those records with last login which is today.

Please reply. :cool:

Points for helpful help.
 

xav0989

Community Public Relation
Community Support
Messages
4,467
Reaction score
95
Points
0
I personally don't add any index fields except for the primary field. Unless you have 10 000 rows, it won't make your queries faster. It will even slow them down.
 

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
It's rather hard to answer this sort of question given so little information. Please post:
  • the tables' definitions,
  • the indices already defined on the tables ("SHOW INDEX FROM <table>;"),
  • the query you run, and (if it's a SELECT query)
  • the output of "EXPLAIN <query>" (2, 3), where <query> is your select statement. This can also be used to refactor the select statement.
Keep in mind that indices can speed up selects but will slow down any query that changes the data in the tables (inserts, updates and deletes). Usually you get data from a table more often than you change it, but if this isn't true an index may not help overall.

Alternatively, you could take the experimental approach. Get the timing from running the query a few thousand/million times without the index, and then with the index. If it's faster without, drop the index.
 
Last edited:

batman1

New Member
Messages
92
Reaction score
0
Points
0
It's rather hard to answer this sort of question given so little information. Please post:
  • the tables' definitions,
  • the indices already defined on the tables ("SHOW INDEX FROM <table>;"),
  • the query you run, and (if it's a SELECT query)
  • the output of "EXPLAIN <query>" (2, 3), where <query> is your select statement. This can also be used to refactor the select statement.
Keep in mind that indices can speed up selects but will slow down any query that changes the data in the tables (inserts, updates and deletes). Usually you get data from a table more often than you change it, but if this isn't true an index may not help overall.

Alternatively, you could take the experimental approach. Get the timing from running the query a few thousand/million times without the index, and then with the index. If it's faster without, drop the index.


your post has been helpful thanks for the reply.
 

xav0989

Community Public Relation
Community Support
Messages
4,467
Reaction score
95
Points
0
Could you still post the results for the benefit of the community?
 

vol7ron

New Member
Messages
434
Reaction score
0
Points
0
...Unless you have 10 000 rows, it won't make your queries faster. It will even slow them down.

Keep in mind that indices can speed up selects but will slow down any query that changes the data in the tables (inserts, updates and deletes). Usually you get data from a table more often than you change it, but if this isn't true an index may not help overall.

Wrong and wrong.

The more records a table has, the more important indices become and yes they do work against smaller tables because the database first scans the data library and finds the index and then scans the physical table for data. If the original table is small enough, then it's faster to perform one table scan then two, but you can't put a number on how many records this occurs at.

The second point is wrong because indices are used and help improve performance on Updates and Deletes in addition to Select queries. They are generally not used on Inserts unless a table join or conditional statement exists because Interts just append a new row to the end of a table.

The important thing to take away is that large tables need indices - I'd put the number starting around 2500 records even though as i said before, there is no real number (especially depending on hardware). But the most important thing to remember is that the only fields that need to be indexed are the ones used in a WHERE clause of a query. Once you set that up, determining what type of index to use (b-tree, etc) is the next step to performance tuning.
 
Last edited:

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
The second point is wrong because indices are used and help improve performance on Updates and Deletes in addition to Select queries. They are generally not used on Inserts unless a table join or conditional statement exists because Interts just append a new row to the end of a table.
You're right in that indices can help with UPDATEs and DELETEs. However, there is a cost to using indices, including the need to update them, which will negatively impact performance. Generally, the trade-off results in a net gain.
 

xav0989

Community Public Relation
Community Support
Messages
4,467
Reaction score
95
Points
0
Wrong and wrong.

The more records a table has, the more important indices become and yes they do work against smaller tables because the database first scans the data library and finds the index and then scans the physical table for data. If the original table is small enough, then it's faster to perform one table scan then two, but you can't put a number on how many records this occurs at.

The second point is wrong because indices are used and help improve performance on Updates and Deletes in addition to Select queries. They are generally not used on Inserts unless a table join or conditional statement exists because Interts just append a new row to the end of a table.

The important thing to take away is that large tables need indices - I'd put the number starting around 2500 records even though as i said before, there is no real number (especially depending on hardware). But the most important thing to remember is that the only fields that need to be indexed are the ones used in a WHERE clause of a query. Once you set that up, determining what type of index to use (b-tree, etc) is the next step to performance tuning.

What I like about the internet is that you always learn something. Just now, I've learned that something that I thought was true for a long time. I won't sleep as dumb tonight! (no sarcasm)
 
Top