From: Ryan N. <nov...@um...> - 2008-08-18 20:47:25
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Technik wrote: >>> Yes, it's possible as long as the index names are different. Sorry, >>> but >>> I don't have your mysql output anymore. But each line in your posted >>> output represents a column in the index. So for each column in the >>> index >>> exists one record. >> Why would one want to indexes on the same set of columns? Or is this >> not >> what's happening? > > No - this is not what happend. At least not in my example. > >>> An example based on my previous post: >>> >>> First record: Primary index (column index:1) >>> Second record: JobId on the column JobId (column index:1) >>> Third record: JobId_2 on the column JobId (column index:1) >>> Fourth record: JobId_2 on the column PathId (column index:2) >>> Fifth record: JobId_2 on the column FilenameId (column index:3) >>> Sixth record: PathId on the column PathId (column index:1) >>> Seventh record: FilenameId on the column FilenameId (column index:1) >> And that is my question. Let's say the second and third record. Why >> does >> one want both of those? They appear to be both indexing the same >> column, >> right? > > The second record belongs to the JobId index with one column and the > third record belongs to the index JobId_2 together with the fourth and > fifth record. Ah! So that index is really an index with three different columns that shows up in that form, and was probably generated by a command that had JobId,PathId,FilenameId in it. Finally makes sense. For whatever reason, it was never clear to me before. > This depends on the application. At some point it makes sense to use > indices with a diffrent set of columns although a column is in more than > one index because of the needs and the performance of the application. > Insert statements are slower with a large number of indices but select > statements are faster with clever indices. > >> I have: >> >> mysql> show index from File; > > In deed - you have to much indices. The indices JobId and file_jpf_idx > are exactly the same with diffrent names. I'm assuming one clue to this, beyond the column names, is the same exact values for Cardinality? This is what gave me pause initially. It seems like at that point, I will also want to create an index like the second record you have listed above, as I will be missing that one in my database. > Please drop the indices file_jpf_idx and file_jobid_idx because these > two are from your first try to resolve your problem (drop index > <indexname> on File;). After that you should repair the table File > (repair table File;). OK, will do. > At this point it would be a good idea to stop the mysql daemon und use > myisamchk to renew the index on the table File. cd into your bacula > database directory (eg. /var/lib/mysql/bacula) and run myisamchk -r > File.MYI or do it for all tables in the database (myisamchk -r *.MYI). OK, I will go with that recommendation. I'm assuming that will do something akin to fragmenting the table? Or a reorganizing I guess. Thanks for all of your help! Hopefully this will be a great help also to the archives, and perhaps it could be an area of documentation that could be spruced up. - -- ---- _ _ _ _ ___ _ _ _ |Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Systems Programmer II |$&| |__| | | |__/ | \| _| |nov...@um... - 973/972.0922 (2-0922) \__/ Univ. of Med. and Dent.|IST/AST - NJMS Medical Science Bldg - C630 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIqd/mmb+gadEcsb4RAnuhAJ9nZDc/gjZtoCQNyFczQ3mPTdHZlQCg2pPd /cribskbr7UMMOlHagYcvuQ= =o8Vg -----END PGP SIGNATURE----- |