Decided this was easy enough to investigate myself, even though I still do not understand the underlying specifics of why (feel free to elaborate?)
The answer is: YES int most definitely creates smaller indexes than BIGINT
I made two tables, first with four unsigned INT columns, second with four unsigned BIGINT
I made a compound index across all four columns for each table.
Then I added a million rows of random unsigned smallints 0-65535 to each table.
(each table has identical data, both numbers and row order)
Then I optimized and flushed both tables just to be certain.
INT
Data 17,000 KiB
Index 31,610 KiB
Total 48,610 KiB
BIGINT
Data 33,000 KiB
Index 56,921 KiB
Total 89,921 KiB
17,408,000 int.MYD
32,368,640 int.MYI
33,792,000 bigint.MYD
58,287,104 bigint.MYI
added:
I was concerned the random data repeated (I found some cases).
So I added a primary column with auto-increment to each table and emptied them. Then I filled each with the numbers from 1 to 1,000,000 in each column, incrementing for each row sequentially.
INT
Data 20,508 KiB
Index 32,301 KiB
Total 52,809 KiB
BIGINT
Data 40,039 KiB
Index 54,178 KiB
Total 94,217 KiB
So not quite 50% savings but definitely adds up, even for index storage.
- Yes, performance may be better if you add those indexes. However, with such a small number of rows, it's quite possible that full table scan is more efficient and optimizer choses not to use any indexes.
- After adding indexes your execution plan will be different, to get a rough estimation of how effective the indexes are you can multiply "Rows" column for each line of output of
explain
- In general, indexes on fields which participate in filtering/join conditions/order/group by improve performance. You also need to take into account selectivity (how many distinct values you have) of the column; if it's too low , the engine will not use it except if it's covering index for a query.
- Foreign key is a constraint; the main purpose of any constraint to enforce some restriction (referential integrity in case of FK). Thus, if you care about integrity of you data, you should add foreign constraint.
The fact that Mysql implicitly creates an index on FK column means better read performance, and bit worse insert/update/delete performance (because index itself has to be updated).
Finally,
My thinking is that since I'm selecting from locations first, ....
is not absolutely correct. Physical processing is not the same as logical; optimizer decides in which order it will process tables involved (as you can see in your output, the engine first accesses tickets
table) and what access method to use. You can control it to some extent with hints though...
*Side note. The way your WHERE
clause written :
WHERE `tickets`.`client_id` = '20'
AND
(
`customers`.`name` LIKE '%Mahoney%'
OR `customers`.`email` LIKE '%Mahoney%'
OR `locations`.`address` LIKE '%Mahoney%'
)
makes your LEFT JOIN customers
behave as INNER JOIN
.*
Update
Never mind my side note, I didn't pay attention you have OR
s with multiple tables.
I hope it was helpful.
Best Answer
This is how MySQL 4.x did this and it used to severely aggravate me.
In fact, there was a formula I computed on how many such index maneuvers were needed
Good News, MySQL 5.x does not do that !!!
If MySQL 5.x did this, I would be a PostgreSQL DBA today (No offense to PostgreSQL, it is an excellent RDBMS in its own right).
UPDATE
Oh my goodness, I read the post !!! That post came from me !!!
I never thought someone would dig this post up.
Please leave stuff like this dead and buried. Now I am having flashbacks !!!