MySQL Indexing and Optimising

We hope you enjoy your visit.

You're currently viewing our forum as a guest. This means you are limited to certain areas of the board and there are some features you can't use. If you join our community, you'll be able to access member-only sections, and use many member-only features such as customizing your profile, sending personal messages, and voting in polls. Registration is simple, fast, and completely free.

Join our community!

If you're already a member please log in to your account to access all of our features:

MySQL Indexing and Optimising
Topic Started: Aug 13 2009, 10:24 AM (311 Views)
Viral.	Aug 13 2009, 10:24 AM Post #1
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	I'm thinking of using 1 table for all users in the next version of the AIO as I've read it is a lot faster performance-wise. However, I also read I need to use MySQL indexing and optimising to make this quicker. I've tried reading some documentation on it but can't really make heads or tails on what I need to do and why I need to do it. Can anyone explain?



mcteeth	Aug 13 2009, 11:18 AM Post #2
Oh, Comely. Posts: 97 Group: Members Member #174,657 Joined: September 19, 2008 I'm Browsing With Firefox 3	This is a pretty good introduction: http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm



Viral.	Aug 13 2009, 11:19 AM Post #3
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	That's the doc I read but I couldn't understand much of it. It seemed to expect you to know what the indexing was going to do and why you need to do it, but I'm not at that stage yet.



Viral.	Aug 15 2009, 09:08 AM Post #4
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	Small bump in case this has gone unnoticed.



Jory	Aug 15 2009, 10:40 AM Post #5
Posts: 2,791 Group: Global Support Team Member #41,171 Joined: January 12, 2005 I'm Browsing With Firefox 3 My Board URL http://zb.jorygeerts.com/	Indexing basically means the DBMS makes an "index" of the columns of your choice. Using this index, it can do lookups much faster. In example, say you need to oftenly find a user by the email address. Your query may be something like this:[sql]SELECT `id`,`name` FROM `users` WHERE `email` = 'john@doe.com' LIMIT 1;[/sql] If you don't have any indexes, MySQL will to a full table scan to search for rows where the email field contains 'john@doe.com'. This means each and every row has to be read. (Untill the first matching row is found, because of the limit.) If you have an index on the email field, MySQL will look at this index instead, which is much faster. (Explaining exactly why and how its faster is probally way beyond what you want to know, at least for now. Plus, I don't understand enough of it myself to give a good explaintation. :P: ) Creating indexes perfectly is pretty hard. If "pretty good" is OK for your needs, just create an index for the fields you will commonly filter the data by (this includes joining tables together!) and you should be pretty much done. Just wondering, how big is the dataset you're working with anyway?



Viral.	Aug 15 2009, 06:14 PM Post #6
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	Well with the current AIO all forums have their own table (with an assigned cd). In the next version I am thinking of doing it so it uses 1 table, with the cd used to separate the different forums. If I convert all members over to the new AIO system, there would be over 23,000 records (1 record per user).



Jory	Aug 16 2009, 11:25 AM Post #7
Posts: 2,791 Group: Global Support Team Member #41,171 Joined: January 12, 2005 I'm Browsing With Firefox 3 My Board URL http://zb.jorygeerts.com/	23K isn't small, but its not huge either. Selects should be reasonable fast even without any indexes. You'll probally want to create an index on the cd field though, if you oftenly need to query all users belonging to a specific forum. If you always query on cd + user-id, create an index on them both (so, one index that has both fields in it.)



Viral.	Aug 16 2009, 11:50 AM Post #8
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	Hmm ok. Now, to create the index, is it something I do in phpMyAdmin, or in an actual query? I'm also under the impression that when I either update/ insert/ delete a row I need to somehow update the index. Would you know how to do that?



mcteeth	Aug 16 2009, 02:46 PM Post #9
Oh, Comely. Posts: 97 Group: Members Member #174,657 Joined: September 19, 2008 I'm Browsing With Firefox 3	You can create an index either from a query (ALTER TABLE table_name ADD INDEX (column_name);) or from phpMyAdmin. To do it from phpMyAdmin, click on the table and look at the row of icons next to each field under "action." It's one of those. Finally, you can add one when you create a table: Code: `CREATE TABLE table_name ( id INT, username VARCHAR(20), password VARCHAR(32), ...and so on..., INDEX(id) );` Note that primary keys, which likely appear on most of your tables, are also indexes themselves. Quote: I'm also under the impression that when I either update/ insert/ delete a row I need to somehow update the index. Indexes are updated automatically, which means that there are some trade-offs. While properly using indexes can make SELECT queries substantially faster, you have to keep in mind that INSERT and UPDATE queries will be slower, because it has to update every index on the table. You just have to consider whether your table is going to be read from or written to more. Edited by mcteeth, Aug 16 2009, 02:56 PM.



Viral.	Aug 16 2009, 04:17 PM Post #10
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	Ahh ok, so that's easy enough to create an index for a standalone column, but what if I wanted to index the rows cd and zbid together, so I can do something like SELECT username FROM table WHERE cd='$cd' AND zbid='$zbid', or do I just add them both as indexes separately? As you see with my select query above, would I have to alter it in any way to use indexes?



Jory	Aug 16 2009, 04:59 PM Post #11
Posts: 2,791 Group: Global Support Team Member #41,171 Joined: January 12, 2005 I'm Browsing With Firefox 3 My Board URL http://zb.jorygeerts.com/	If you often use both cd and zbid in the where clause, make an index that contains both the fields. The query would be ALTER TABLE table_name ADD INDEX (cd, zbid);. If you want to do it in phpMyAdmin, you can't use the icons behind the columns, but there is a button somewhere at the bottom of the page with an input field for how many fields the index will be.



Viral.	Aug 16 2009, 05:16 PM Post #12
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	So, let's say I create an index for cd and zbid using ADD INDEX (cd, zbid);, which of the following queries would it affect: Code: `SELECT * FROM table_name WHERE cd='$cd' and zbid='$zbid'` Code: `SELECT * FROM table_name WHERE cd='$cd'` Code: `SELECT * FROM table_name WHERE cd='$cd' AND zbid='$zbid' AND username='Viral'` I know it will affect the first, it shouldn't affect the second but as for the third one, will it go through the indexed results and then look for any of those rows with username='Viral', or will it search through the whole table?



mcteeth	Aug 16 2009, 05:44 PM Post #13
Oh, Comely. Posts: 97 Group: Members Member #174,657 Joined: September 19, 2008 I'm Browsing With Firefox 3	There's a simple way to find out: Create a test table with some fields and indexes, populate it with some rows, use EXPLAIN in front of the SELECT, and it'll tell you which indexes (if any) are being used. In your examples, all three will actually make use of the index pair. (However, if you tried SELECT FROM table_name WHERE zbid='whatever'*, the index wouldn't be used. This is a good time to go back and read the article I posted again. It should be a lot easier to read through and it goes a bit more in depth on this information. )



Viral.	Aug 16 2009, 07:31 PM Post #14
Member Posts: 3,097 Group: Members Member #65,614 Joined: August 19, 2005 I'm Browsing With Chrome My Board URL http://zbaio.zetabin.com/	Thanks, I've re-read it and I understand it a lot more now. I'll be starting the base for the AIO 2 using indexes so let's hope it works out well .



Ethyr	Sep 5 2009, 11:19 AM Post #15
Light of a Goddess Posts: 463 Group: Members Member #27,177 Joined: August 13, 2004 I'm Browsing With Firefox 3	And, just to add something to this topic, with you using the SELECT statement, and using WHERE using Primary Keys and/or foreign keys, it already indexed. Primary Keys (As I think Jory stated) is actually an index made, b/c there can be only one primary key. So, for instance, your one where you select x rows WHERE cid="whatever" and zbid="whatever" will point exactly to that spot, since they are already indexed. Some tidbits.



1 user reading this topic (1 Guest and 0 Anonymous)


« Previous Topic · Programming and Scripting Chat · Next Topic »