Some mySQL Tips

[dbest]

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

Some good tips listed there.

I found the first one really useful, as I didn't know about the effect of mysql functions in query caching earlier.

Hope some of you guys find it useful too.

[Nox]

Hmmm.... didn't expect it but there are really many nice tips (many things on websites like tutsplus or smashingmag. seems to me written quickly and very basicly)

I was suspicious about ORDER BY Rand() but this showed a nice trick how to do it better

26.10. 2009
How to perform multiple updates in one query

Code: [Select]

<?php
$prices = array(); // $id => $prices
// filling of the array here
mysql_query("
    UPDATE products
    SET price = CASE id" . make_when($prices) . " END
    WHERE id IN (" . implode(", ", array_keys($prices)) . ")
");?>
Speed of this query in InnoDB compared to multiple updates in a single transaction - cca double,
compared to multiple updates not packed in a single transaction - around hundred times faster.

Source: [EN]

27.10. 2009
How to use next auto_increment in a query

Code: [Select]

INSERT INTO `my_table` (nazev,popis) SELECT CONCAT(`AUTO_INCREMENT`,'$file_name'), '$description'
FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`='my_database' AND TABLE_NAME = 'my_table'
Source: [EN]

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[JGadrow]

As far as I know, there is no way to index that query. However, you can look into using a "view" to handle that particular query if it's utilized often.

Also, indexes work well for things that you "search" a lot such as in WHERE or ON conditions. In your example, it's probably best if you simply perform the arithmetic in your application instead of in the database server.

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[travo]

Thanks for the autoincrement trick, so many times Ive foolishly used mysql_insert_id() or whatever it is...

[Nox]

Eeem, that's a bit misunderstood - mysql_insert_id() is fine, but it contains id of the LAST EXECUTED query and it's not accessible in the insert query itself (yet) and that was a bypass. If you need id afterwards, it's fine to use the function

[Chris]

Some questions and advices:


1. Optimize Your Queries For the Query Cache
A note, cache is not very good in BBGs since there are lots of reads. Some even suggest to disable query cache for heavy write tables.

3. LIMIT 1 When Getting a Unique Row
If the field is specified as UNIQUE (like AUTO_INCREMENT) then you don't have to use it since SQL already knows there is only 1 available.

4. Index the Search Fields
I have a dilemma here. I have one field that is updated extremely frequently, also it is used frequently as ORDER BY. Should I put index on that field (which means slower writes but faster order by)?

5. Index and Use Same Column Types for Joins
What if one is SMALLINT and the other is TINYINT? Are these different then?

7. Avoid SELECT *
What if we need all fields? Should we list them all instead of * (to avoid reading field names)?

8. Almost Always Have an id Field
I agree, these come by handy very frequently even if you never expected it

15. Fixed-length (Static) Tables are Faster
Almost always forgotten. Just make username CHAR(32) password CHAR(12) instead if VARCHAR(x) and SQL will always know where the next record in the file starts. It makes sense only if WHOLE table is fixed width, if you have even one dynamic the whole table is dynamic. I've heard it is irrelevant for InnoDB since it stores it different way.

16. Vertical Partitioning
The obvious choice for BBG is separate table for password and email since it is needed only upon login and the game does not need it.

16a. Horizontal Partitioning
They haven't listed it but it is worth mentioning. If you do it bear in mind that some fuctiuonality won't work (like bit operations). Consult your SQL version for the limitations.

17. Split the Big DELETE or INSERT Queries
You won't have "big" in BBGs. But if your game proves otherwise then you already know all about SQL and you don't read this topic

18. Smaller Columns Are Faster
You might think that your small game will never exceed 65536 players. Well, don't forget about inactives and deleted accounts. It is just a matter of time. So full 32 integer for userid is really recommended (and if your game happenes to be extremely small then the whole performace thing is a waste of time for you and there is no point for you to not use 32 bit anyway .

19. Choose the Right Storage Engine
My religious alignment is MyISAM You obviously can find abundance of belivers on both sides

[tellmore]

YEY, I have something to add too, and thistime its worth to read it

Choose the correct data type for columns.
Say.. a column that stores only small numbers may not use int, can happen that tinyint is sufficient.
mysql data types

Supposedly 10 columns of bigint will be slower than 10 columns of tinyint.

[aerosuidae]

4. Index the Search Fields
I have a dilemma here. I have one field that is updated extremely frequently, also it is used frequently as ORDER BY. Should I put index on that field (which means slower writes but faster order by)?

Is it a bottle neck at all?  If so, is the bottle neck during reading or writing?  You mentioned you use MyISAM, which takes a table level lock when writing; slowing down writes will block other queries for longer...

5. Index and Use Same Column Types for Joins
What if one is SMALLINT and the other is TINYINT? Are these different then?

SMALLINT = TINYINT is fine.  The tinyint will be padded out during comparison, but this is virtually zero overhead.

7. Avoid SELECT *
What if we need all fields? Should we list them all instead of * (to avoid reading field names)?

If you definitely need them all, just use SELECT * -- underneath it's the same as listing all the fields manually.  This tip is just a way to reduce the volume of data read from disk and sent over the wire, in turn reducing memory requirements on both server and client.  Less data is generally faster and therefore better.

Another important thing to think about with SELECT * is: What happens in the future if the table is changed and fields are added or removed?  Listing fields explicitly in queries means that you immediately catch exactly which queries depended in the changed fields, if you forgot to change them too.  Using SELECT * means the forgotten queries can fall under the radar and become much more troublesome bugs to hunt down later.  This is a very situation specific example, true, but you get the idea.

18. Smaller Columns Are Faster
You might think that your small game will never exceed 65536 players. Well, don't forget about inactives and deleted accounts. It is just a matter of time. So full 32 integer for userid is really recommended (and if your game happenes to be extremely small then the whole performace thing is a waste of time for you and there is no point for you to not use 32 bit anyway .

Actually this is sometimes a lie anyway! (The "Smaller Columns Are Faster" tip, not what you said Chris ).  Smaller columns being faster is very storage engine dependent.  Take MEDIUMINT for example; MyISAM benefits from using MEDIUMINT instead of INT because it makes MEDIUMINT 16bit. But InnoDB just converts MEDIUMINT to INT internally anyway.

With MySQL, you can usually narrow your integer type choices to TINYINT, INT, or BIGINT and ignore the rest.  This rule of thumb generally fits well with most popular storage engines, and also makes it harder to fall into the types of overflow trap Chris is talking about.

[dsheroh]

Another important thing to think about with SELECT * is: What happens in the future if the table is changed and fields are added or removed?  Listing fields explicitly in queries means that you immediately catch exactly which queries depended in the changed fields, if you forgot to change them too.  Using SELECT * means the forgotten queries can fall under the radar and become much more troublesome bugs to hunt down later.  This is a very situation specific example, true, but you get the idea.

This (not performance) is why I never use * in queries whose results will be interpreted by a program.  All it takes is one added field in the middle of a table or one deleted field and your application will go off into the Land of Undefined Behavior because some of the variables populated from the query will be getting populated from the wrong fields.

If, on the other hand, you individually list out every field to be retrieved, then it doesn't matter if their order changes (the query will get them in the named order regardless of their order in the underlying table) or something is added (if it's not named, it won't get retrieved) and, if one is deleted, you'll find out immediately that it was important to your code (because the query will fail with a 'missing field' error when run).  You also get the added side benefit of having to think about which fields are actually needed and thinking more about what your code is doing and what data it's using to do it is rarely a bad thing.

[Nox]

1. Optimize Your Queries For the Query Cache
A note, cache is not very good in BBGs since there are lots of reads. Some even suggest to disable query cache for heavy write tables.

Since there are lots of reads cache IS good, right? But maybe you meant frequent inserts/updates
Yea, I think that would be in cases where there's also no reason to create index, like some log files which you only use as admin, heavy writes but scarce reads

15. Fixed-length (Static) Tables are Faster
Almost always forgotten. Just make username CHAR(32) password CHAR(12) instead if VARCHAR(x) and SQL will always know where the next record in the file starts. It makes sense only if WHOLE table is fixed width, if you have even one dynamic the whole table is dynamic. I've heard it is irrelevant for InnoDB since it stores it different way.

This is most pleasant in cases when you already know the length of the text, it's great for columns with tokens, hashes etc. since they have constant length

[Mufasa]

can't believe I missed this thread. Some really good tips both here and in that article (never knew about the auto increment in a query or doing a where based on an array of entries)

[Meep]

I found this really helpful, thanks!

[SirStompsalot]

For MySQL, you may also want to look into Unsigned vs Signed. A signed/unsigned number determines the range of possible values.

For instance, in my game, I KNOW I'm never, ever going to have a number that's below zero. For a tinyint, the signed range is -128 to 127. However, if I change this column to an unsigned range, I'll have 0 to 255. Quite a powerful technique when you need bigger numbers in smaller spaces.

Just a note: as far as I know, the SQL Standard is oblivious to the concept of an unsigned integer. So if you're ever going to migrate to a database like DB2, you will run into an issue.

[Chris]

For MySQL, you may also want to look into Unsigned vs Signed. A signed/unsigned number determines the range of possible values.

For instance, in my game, I KNOW I'm never, ever going to have a number that's below zero. For a tinyint, the signed range is -128 to 127. However, if I change this column to an unsigned range, I'll have 0 to 255. Quite a powerful technique when you need bigger numbers in smaller spaces.

Just a note: as far as I know, the SQL Standard is oblivious to the concept of an unsigned integer. So if you're ever going to migrate to a database like DB2, you will run into an issue.

Be aware of MySQL 5 overflow (in MySQL 4 it was safe to substract unsigned, in 5 you might get overflow; like "10-11=255" instead or "10-11=0")

[Mufasa]

Yes, I've hit that before, and it's resulted in some players with huge bank balances!

[Meep]

Thanks, there are some great tips ^^