SQL Database Optimization

[batman1]

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.

Points for helpful help.

 

[xav0989]

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]

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.

 

[batman1]

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]

Could you still post the results for the benefit of the community?

 

[vol7ron]

...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.

 

[misson]

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]

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)