Creating table indexes in MySQL
Creating an index for specific columns in a database table can greatly speed up search operations. This is important for any database query that use WHERE in the SQL string. Below is a practical example used to speed up a Coppermine Photo Gallery MySQL database.
Connect to MySQL:
mysql -u -p
Get a list of databases:
show databases;
Select your database:
connect ;
Get a list of tables:
show tables;
The table for this example is called photoarc_pictures. This is a standard Coppermine Photo Gallery table that contains names of files, filepaths, and photo captions. Let’s get some details about this table:
describe photoarc_pictures;
I have a script that adds a caption for every photo in the database. The SQL portion of the script looks something like this:
UPDATE photoarc_pictures SET caption = "$CAPTION" WHERE filename = "$FILENAME" AND filepath = "$FILEPATH";
As you can see, this SQL query tries to search for a specific filepath and filename. If you have a large table, this operation may take some time and a lot of CPU power. What we need to do is to build an index for filepath and filename columns. Here’s how to do this:
CREATE INDEX filepath_index ON photoarc_pictures (filepath); CREATE INDEX filename_index ON photoarc_pictures (filename);
In this case the filepath and filename columns are varchar type. If they were BLOB values, the syntax for index creation should specify how many charactes to use:
CREATE INDEX filepath_index ON photoarc_pictures (filepath(255)); CREATE INDEX filename_index ON photoarc_pictures (filename(255));
This may take some time to complete for large table. Once it’s done, you will notice a significant improvement in performance for all SQL operations that try to match a specific filepath and filename value in this table.
If you modify the layout of your table, such as rearrange the column positions, you will need to re-build your indexes. To re-create an index, you first need to delete the existing index. In our example this would be done like so:
DROP INDEX filepath_index ON photoarc_pictures; DROP INDEX filename_index ON photoarc_pictures;
Indexes are a common part of any database, but they are often overlooked. The improvement in performance, brought about by a properly indexed data, can be substantial.
9 Comments »
2 Pingbacks »
-
[…] Once you index a table, every time new data is added to that table, your database software will automatically update the index. For more information about indexing MySQL tables see Creating table indexes in MySQL. […]
-
[…] Creating table indexes in MySQL […]
![wyryr1sn52gntzn65v4fpz5lc6v[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/wyryr1sn52gntzn65v4fpz5lc6v1-80x80.jpg "Plundering Facebook Photo Albums")
![clfud46mxf2t5zph2l04zlpgl1d[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/clfud46mxf2t5zph2l04zlpgl1d1-80x80.jpg "Scraping a Web Page in Bash")
![DSCF5361[1]](https://i1.wp.com/www.krazyworks.com/wp-content/uploads/2020/10/DSCF53611-80x80.jpg "Processing Videos with ffmpeg and Lightroom")
![Cat-Devouring-A-Bird-Pablo-Picasso[1]](https://i0.wp.com/www.krazyworks.com/wp-content/uploads/2020/10/Cat-Devouring-A-Bird-Pablo-Picasso1-80x80.jpg "Searching Twitter")
![91ebozy1e3b138waujy28agrngy[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/91ebozy1e3b138waujy28agrngy1-80x80.jpg "Generating Random Text Files for Testing")
![iwoau71okwxqh4jv3k4ook32qps[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/iwoau71okwxqh4jv3k4ook32qps1-80x80.jpg "Finding Cron Jobs")
![5gtysva4wkc8mzc1k3e66vaycxx[1]](https://i1.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/5gtysva4wkc8mzc1k3e66vaycxx1-80x80.jpg "Understanding Memory Utilization in Linux")
![sy2zeb1vp2h8itlxfbbylw0jrm0[1]](https://i0.wp.com/www.krazyworks.com/wp-content/uploads/2019/12/sy2zeb1vp2h8itlxfbbylw0jrm01-80x80.jpg "Validating HTTPS Cache Peers for Squid")
![Photo-2019-10-07-14-39-03_0266[1]](https://i1.wp.com/www.krazyworks.com/wp-content/uploads/2019/11/Photo-2019-10-07-14-39-03_02661-80x80.jpg "Verifying SNMP Connectivity on Multiple Hosts")
Hi there, I was looking around for a while searching for unsecured wireless networks and I happened upon this site and your post regarding , I will definitely this to my unsecured wireless networks bookmarks!
I want to use in-memory tables for improved performance in MySQL. But what is the cleanest way to ensure that the tables reload (from on-disk tables) when the MySQL server restarts? Is there any way to configure this natively within MySQL, without resorting to Perl and similar external script hacks? Thanks
Hi, Im practicing in mysql and Im making a database. But What it keeps saying not indexed, what do I choose Unique or primary ?
and when it ask you to create the TABLE , do I name the first column the same name as the table name? Because it keeps saying error table 1 ?
Thanks!
thanks for helping, so should i make it unique?
I dont have a lock at all, is that okay? Im just practicing..this is my first time trying it out and its NOT the same stuff I learned on the 3schools site, so Iam confused from the start :) Please help, what do I do about the lock? Thansk
I want to make it so that people can search my mysql database. I thought of using WHERE field=’%$search%’ or LIKE but then if someone searched it would only search for that exact phrase for example if they searced for 1 2 and one of the entrys had 1 in it it would not find it because it would need to have 1 2 exactly not 1 or 2. How can i make it search so that it finds stuff even if it is not exactly the way they tiped it.
How can I efficiently get current information from a mysql database in my c# program?
The program runs on several systems at once and each system needs to get fresh information in case the other systems have made changes.
The program, in addition to normal operations that update the database based upon user activity, needs to draw out updated information from up to 5 tables simultaneously and display them to the user in a listbox.
I need to have my users create username, and passwords, and i want thier email addresses. Everyone says to create a mysql database in phpadmin. I click datatabase then create new database, and when I try to add tables for each item i need it requests an index, then I keep getting error messages? Please tell me step by step how to create a new database in localhost/phpmyadmin?
I would like a database that just allows storing data through forms and displaying it again. I have looked, but don’t really know what to look under. PHPMyAdmin is actually very close to what I want, but I need it to be for an end-user, not a developer. I would prefer Open Source if possible.
Suppose you created indexes on some fields of a table using a SQL command, then one would want to see the existing indexes. How would one do this by PHPMyAdmin. I would be so grateful, if one would tell me how
Okay, i have a mysql database with a table called cmspage. in that i have id, title, desc, keywords, content.
But how do i get it so for every new id is a new page.. ie:
id=1 index..php
id=2 about.php
etc etc but does it automatically?