mysql keys

corblimey

I'm trying to make my bloated app run a little faster and I've been fiddling with the keys on my bigger tables. I went through all of my SELECTs and tried to put in a key that would make things a little better. I have a table A consisting of 4 fields:

a_id int(11) auto_increment PRIMARY KEY
a_company char(1)
a_created int(11)
a_sent int(11)

At various points through my code, I want to find records where the company code matches a_company and either the a_created is blank or the a_sent is blank (never both at the same time). To speed this up, I added 2 keys:

KEY a_created (a_company, a_created)
KEY a_sent (a_company, a_sent)

phpMyAdmin complains about this, telling me that "More than one INDEX key was created for column a_company". Fair enough, it's only a warning, but is it having dire effect on the efficiency of my keys? Is there a better way to implement this?

jimmycrackcorm

I'd just create an index for each of the three fields instead of compound indexes.

MIN2511

jimmycrackcorm wrote: »

I'd just create an index for each of the three fields instead of compound indexes.

I agree with that, having just started learning SQL i found that indexes make life easier

sh_o

use the 'explain' command before your selects on a sql worksheet that are taking a long time to get some insight into where you should be placing your indexes. the explain command is invaluable for performance tuning.

corblimey

sh_o wrote: »

use the 'explain' command before your selects on a sql worksheet that are taking a long time to get some insight into where you should be placing your indexes. the explain command is invaluable for performance tuning.

That never occurred to me, I just matched the indices to the where clauses. My only issue with it is that I only have access to small amounts of test data which take no time at all, but in production, there's tens of thousands of records.

amen

when you say blank do you mean blank or Null (i'm assuming null as its an INT)

democrates

Assuming a_created and a_sent are to be 1 or 0 denoting true or false respectively, I'd change them to TINYINT, the space saving is not just on the table but even better, on the indexes.

Also I'd have them as NOT NULL, because NULLS introduce a risk of errors, EG: in a left join, is a value from the second table column null because it's null on a returned row, or because no row was returned?

It might be worth considering combining a_created and a_sent into say a_status, where value meanings are say 0 = unprocessed, 1 = created, 2 = sent, 3 = created and sent (depending on what business rules allow). If in the future it is decided to add say "returned" as a possible value, you only need to add a row to the status table, and your code needn't change as it's presenting say a drop-down box on a form driven by the status table. Just a thought.

corblimey

democrates wrote: »

Assuming a_created and a_sent are to be 1 or 0 denoting true or false respectively, I'd change them to TINYINT, the space saving is not just on the table but even better, on the indexes.

These 2 fields are basically holding date/timestamps. So in this case, I want to return all records where (something) has not been created (a_created is blank) or where it has been created but not sent, or it has been created and sent.

Hence the extra keys.

democrates

corblimey wrote: »

These 2 fields are basically holding date/timestamps. So in this case, I want to return all records where (something) has not been created (a_created is blank) or where it has been created but not sent, or it has been created and sent.

Hence the extra keys.

Ah right, then I'd use the DATE or DATETIME type for them, the format for DATETIME is yyyy-mm-dd hh:mm:ss and default value 0000-00-00 00:00:00
That way you can use date/time formatting functions in future sql.

For dba purposes I tend to also have an 'added' field for records of the TIMESTAMP type defaulting to the current-timestamp. This can be a lifesaver eg, records entered from-to need to be changed/reported.