![]() The query is also about 317 times faster than the original. This reduces the number of rows scanned to 200,000. After that, the pictures are quickly located using the indexed album_id column. First, all the albums are scanned to find the ones that belong to the user. Now if you run the query, the process no longer involves scanning the entire list of pictures. For example, you can add an index on picture.album_id like this: ALTER TABLE picture ADD INDEX(album_id) Use indexes to avoid unnecessary passes through tables. ![]() You can either flip through all the pages, or you can pull on the right letter tab to quickly locate the name you need. Think of data as being names in an address book. You can significantly increase performance by using indexes. However, you can make this process much more efficient. This means that it actually scans 40 billion rows for the album table. It scans 2,000,000 pictures, then, for each picture, it scans 20,000 albums. The important pieces here are the table name, the key used, and the number of rows scanned during the execution of the query. You see a row for each table that was involved in the query: The result you get is an explanation of how data is accessed. LEFT JOIN album ON picture.album_id = album.id You simply prefix the query like this: EXPLAIN SELECT picture.id, picture.title It works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. One tool that MySQL offers is the EXPLAIN keyword. Once you know which are the offending queries, you can start exploring what makes them slow.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |