Db performance

Databases
1

  1. when a user logins, my script query db for username and password and status to see if user us active. Username col is unique. For better db performance does it make sense that I set status as index? Status value us just 0 and 1 for pending and active.

  2. I have two cols for start and end date and values are unix timestamp. I query current time between start and end date. Does it nake sense to set start and end date as index for better db perfirmance?

2

Depends, whats the exact query you are using to check the user?

If commonly queried, then absolutely.

What I like to go back through all of my queries I run, pull out the WHERE clause into a text file, 1 per line and have a quick glance at whats commonly used. Try to build as few indexes as possible to suite them all. For example:


users WHERE username = '$user'
users WHERE username = '$user' AND status = '$status'
other_table WHERE startdate = '$start'
other_table WHERE startdate >= '$start' AND enddate <= '$end'

I would make a single index for users with username, status (username first status second because that’s the order in the query) and a single index on other_table startdate, enddate for the same reason.

Now if I have a new query come up looking only at enddate, that index really wont satisfy the query because it looks at startdate first.

EDIT: And I found this gem for you: http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

3

I use phpMyAdmin to set a col as index. I just click the index link and it sets it. So how can I set first and second priority for indexes? Just clicking in priority does the job?

4

I would assume that phpmyadmin is creating multiple indexes here, rather than one. You’ll probably want to use SQL to create your indexes: http://dev.mysql.com/doc/refman/5.7/en/create-index.html

Indexes will make and break your application (depending on the size and traffic) so be sure to put some though into this.

5

What are dis/advantages of having multiple indices rather than one?

6

Well, you have to understand what an index really is and what it is doing. The db engine will try to store as much of the ‘working set’ into random access memory as it can. As you probably know, RAM is magnitudes faster to read / write from and to than disk. Creating too many indexes can drop the performance of all indexes. Not creating a proper index for a query will simply result in it having to read entirely from disk.